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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.