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.
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.