CODEWITHSUNDEEP
sqlsas
Michael
Michael

Reputation: 75

Separating variables in a SAS data set

I have a question about separating variables in a data set in a specific way. When we did field work, we had to collect data in a method that looked like this:

Range Row  HGT  V   HGT2  V2  HGT3  V3  HGT4  V4
1     2    151  15  127   22  114   16  97    12

In reality, the variables there are not different types of measurements, but different distances from a start point. Because of this, I want to get the data into a form like this:

Range Row  HGT  V   HGT2  V2  HGT3  V3  HGT4  V4
1     2    151  15  .     .   .     .   .     .
1     2    .    .   127   22  .     .   .     .
1     2    .    .   .     .   114   16  .     .
1     2    .    .   .     .   .     .   97    12

This way, I can use a bunch of if-then statements to put in the true rows for each line of data, since range/row is how we identify everything, and compress the data back into the 2 variables with a coalesce statement in sql. I know that this can easily be done in excel by hand, but our lab head is strongly against that due to the risk of us making mistakes.

Upvotes: 0

Views: 92

Answers (3)

DaBigNikoladze
DaBigNikoladze

Reputation: 661

Here an example for the first 2 indexes.

data newData (drop=i j Vold V2old HGTold HGT2old);
  format HGT V HGT2 V2;
  set oldData (rename=(V=Vold V2=V2old HGT=HGTold HGT2=HGT2old));
  array arrVold[2] Vold V2old;
  array arrVnew[2] V V2;
  array arrHold[2] HGTold HGT2old;
  array arrHnew[2] HGT HGT2;
  do i=1 to dim(arrVold);
    do j=1 to dim(arrVold);
      arrHnew[j]=.;
      arrVnew[j]=.;
    end;
    arrHnew[i]=arrHold[i];
    arrVnew[i]=arrVold[i];
    output;
  end;
run;

Upvotes: 0

Kay
Kay

Reputation: 365

If the the number of variables are certain then you can follow this method.

Assumption: Source data is "Range_data"

data Range_data_1(keep=Range Row HGT V);
    set Range_data;
run;

data Range_data_2(keep=Range Row HGT2 V2);
    set Range_data;
run;

data Range_data_3(keep=Range Row HGT3 V3);
    set Range_data;
run;

data Range_data_4(keep=Range Row HGT4 V4);
    set Range_data;
run;

data All_Range;
    set Range_data_1 Range_data_2 Range_data_3 Range_data_4;
run;

Suggestions:

  1. You try writing a macro if the number of HGT and V variables are indefinite.

  2. Check you table design also once.

Upvotes: 1

Hugh Jones
Hugh Jones

Reputation: 2694

Does this help?

SELECT RANGE, ROW, HGT, V,  NULL AS HGT2, NULL AS  V2, NULL AS  HGT3, NULL AS  V3, NULL AS  HGT4, NULL AS  V4    
UNION
SELECT RANGE, ROW,  NULL AS HGT, NULL AS  V, HGT2, V2, NULL AS  HGT3, NULL AS  V3, NULL AS  HGT4, NULL AS  V4
UNION
SELECT RANGE, ROW,  NULL AS HGT, NULL AS  V,  NULL AS HGT2, NULL AS  V2, HGT3, V3, NULL AS  HGT4, NULL AS  V4
UNION
SELECT RANGE, ROW,  NULL AS HGT, NULL AS  V,  NULL AS HGT2, NULL AS  V2, NULL AS  HGT3, NULL AS  V3, HGT4, V4

Upvotes: 0

Related Questions