Transpose data whith array and conditions

[mansour_ib_sas]

Hello,

My objectif  in this work is to create a new table with only 3 columns.

when the value of the observation contains v1 it will be in col1 is so on.

I made this program, it works correctly.

I wonder if you have other solutions to offer me.

Thank you .

data test;
infile cards dlm=' ' ;
input (var1-var9) ($7.);

cards;
Obs1V1 Obs1V2 Obs1V3 Obs2V1 Obs2V2 Obs2V3 Obs3V1 Obs3V2 Obs3V3
Obs4V1 Obs4V2 Obs4V3 Obs5V1 Obs5V2 Obs5V3 Obs6V1 Obs6V2 Obs6V3
Obs7V1 Obs7V2 Obs7V3
; run;

data test1(drop=var1-var9);
set test;
array vec(*) var1-var9;
array col(3) $ col1-col3;
do i=1 to dim(vec);
 if find(vec(i),'v1','i') ge 1 then 
 col1=vec(i);
  if find(vec(i),'v2','i') ge 1 then 
 col2=vec(i);
 if find(vec(i),'v3','i') ge 1 then 
 col3=vec(i);
 if i in (3,6, 9) then 
output;
end;
run;

[ballardw · Accepted solution]

Read it differently?

data test;
infile cards dlm=' ' ;
input (col1-col3) ($) @@ ;

cards;
Obs1V1 Obs1V2 Obs1V3 Obs2V1 Obs2V2 Obs2V3 Obs3V1 Obs3V2 Obs3V3
Obs4V1 Obs4V2 Obs4V3 Obs5V1 Obs5V2 Obs5V3 Obs6V1 Obs6V2 Obs6V3
Obs7V1 Obs7V2 Obs7V3
; 
run;

Perhaps you need to give a bit more realistic example of the data. If the V1, V2 And V3 actually appear in different order than 1  2  3 then show some values out of order. Your example data does not actually require the Find.

data test1(drop=var1-var9);
set test;
array vec(*) var1-var9;
array col(3) $ col1-col3;
do i=1 to dim(vec);
 col(mod(i,3) +1)=vec(i);
 if i in (3,6, 9) and not missing(vec(i)) then output;
end;
run;

Might still be done by reading and then sorting:

data test;
infile cards dlm=' ' ;
input (col1-col3) ($) @@ ;
array c col1-col3;
call sortc(of c(*));

cards;
Obs1V2 Obs1V1 Obs1V3 Obs2V3 Obs2V2 Obs2V1 Obs3V1 Obs3V2 Obs3V3
Obs4V1 Obs4V2 Obs4V3 Obs5V1 Obs5V2 Obs5V3 Obs6V1 Obs6V2 Obs6V3
Obs7V1 Obs7V2 Obs7V3
; 
run;

[mkeintz]

If you are starting with raw data, I would go wilth @ballardw 's last suggestion.

Here is the equivalent if you already have data set TEST:

data test;
infile cards dlm=' ' ;
input (var1-var9) ($7.);
cards;
Obs1V1 Obs1V2 Obs1V3 Obs2V1 Obs2V2 Obs2V3 Obs3V1 Obs3V2 Obs3V3
Obs4V1 Obs4V2 Obs4V3 Obs5V1 Obs5V2 Obs5V3 Obs6V1 Obs6V2 Obs6V3
Obs7V1 Obs7V2 Obs7V3
;

data test1 (where=(cats(var1,var2,var3)^=' '));
  set test (keep=var1-var3);
  output;
  set test (keep=var4-var6 rename=(var4=var1 var5=var2 var6=var3));
  output;
  set test (keep=var7-var9 rename=(var7=var1 var8=var2 var9=var3));
  output;
run;