Try this:
proc sql noprint;
select distinct(battrid) into :groups separated by " " from lbload;
quit;
data _null_;
groups = "&groups";
call execute("data ");
do i = 1 to countw(groups);
call execute(scan(groups,i)!!" ");
end;
call execute("; set lbload; select(battrid);");
do i = 1 to countw(groups);
call execute('when("'!!scan(groups,i)!!'") output '!!scan(groups,i)!!';');
end;
call execute('end; run;');
run;
This is of course completely untested and depends on the number of distinct battrid's to work; with high cardinality, you might run into memory problems. If you only have 8, that should work like a charm.
Another method would be to use call execute to create a single data step for each group, but that would multiply the scans through lbload, therefore be less performant.
Edit: added "end" in final call execute
Worked fine with test data from
data lbload;
input battrid $ value;
cards;
HEMA 1
HB 1
HEMA 2
WBC 1
CHEM 1
SGPT 1
CHEM 2
SGOT 1
;
run;
Why do you not use an industry standard data structure such as CDISC LAB, LB, ADLB models? You will find your programming much easier to use normalised data (i.e. test - result per row, rather than in columns) as you can then use by group processing - i.e. no need to create separate files, know what columns are called etc.
https://www.cdisc.org/standards/foundational/lab
For example, to print your multiple datasets you would need a step for each datastep, for me using normalised data and by groups:
proc print data=have; by parcat; title "Category=#byval1"; run;
Will produce print output of the data separated by the parameter categories, far easier to maintain.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.