Hello SAS users, I have a table with many lab variables. A sample dataset is pasted below along with the code I developed. Each lab variables has a character variable type. The whole idea is to truncate lab values and assign separate variables for them. Currently, I have lab variables ranging from lab1- lab40 (I only developed code for lab1- lab4). If I develop similar code for 40 lab variables it would be much lengthier. I thought the code could be simplified further. Can you please suggest any better program. My output should look like the "final" data set created from proc sql statement and should have ability to query 40 lab variables. data have;
input id lab1 $ lab2 $lab3 $ lab4 $;
datalines;
1 2 5 10 124
2 25 305 4054 8
3 29 34 40 92
4 301 5004 2015 20
;run;
%macro want (infile= , var= , outfile= );
Data &outfile;
set &infile;
array vars &var.var1-&var.var4;
start = 1;
do i = 1 to 4;
pos = anydigit(&var, start);
if pos then do;
if pos = start then vars{i} = input(substr(&var,start,1),1.);
else vars{i}= input(substr(&var,start,2),2.);
start = pos+1;
end;
end;
drop i start pos;
keep ID &var &var.var1 &var.var2 &var.var3 &var.var4;
run;
%mend;
%want (infile= have, var= lab1, outfile= want1);
%want (infile= have, var= lab2, outfile= want2);
%want (infile= have, var= lab3, outfile= want3);
%want (infile= have, var= lab4, outfile= want4);
proc sql;
create table final as
select a.id, b.lab1, b.lab1var1, b.lab1var2, b.lab1var3, b.lab1var4,
c.lab2, c.lab2var1, c.lab2var2, c.lab2var3, c.lab2var4,
d.lab3, d.lab3var1, d.lab3var2, d.lab3var3, d.lab3var4,
e.lab4, e.lab4var1, e.lab4var2, e.lab4var3, e.lab4var4
from have a
left join want1 b
on a.id= b.id
left join want2 c
on a.id= c.id
left join want3 d
on a.id= d.id
left join want4 e
on a.id= e.id;
quit; Thank you, Chriscoarsen
... View more