data have;
infile datalines truncover;
input @1 deptid 1. deptdescription $ & 3-20;
datalines;
1 Maths
2 Astronomy
1 Maths
3 Physics
4 Public Health
3 Physics
;
run;
/* Sort by Deptid so first./last. logic can be used in the next step */
proc sort data=have;
by deptid;
run;
/* CALL SYMPUTX creates macro variables &Subj1, &Subj2, etc from the values of Deptdescription.*/
/* The total unique values of Deptdescription are stored in &Total */
data _null_;
set have;
by deptid;
if first.deptid then do;
i+1;
call symputx('subj'|| left(put(i,2.)),deptdescription);
end;
if last.deptid then call symputx('total',i);
run;
/* Within a macro %DO loop the data sets are created and those observations with the same */
/* Deptdescription values are read with a WHERE clause */
%macro test;
%do i=1 %to &total;
data &&subj&i;
set have;
where deptdescription="&&subj&i";
run;
%end;
%mend test;
/* invoke the macro */
%test
The above process creates separate datasets based on the deptid and deptdescription. The issue is Public Health is created twice (Public then Health) in separate datasets. I tried using
length deptdescription $50; in the macro test area however I still get separate datasets whenever there is a space in the variable name. How can I address this?
If Subj = Public Health this becomes
data &&subj&i.;
becomes:
data public health; <- This is not valid
Figure out how you want to name the data set and fix that step.
This post addresses the issue you're seeing
https://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/
You have two possibilities: Either you change the variable to a valid SAS version 7 (or earlier) name, or you use option VALIDVARNAME=ANY and use a quoted string for your data set name. The second solution may be easier, as you can use the same macro variable for the WHERE clause and the data set name:
data "&&subj&i"n; set have; where deptdescription="&&subj&i";
So your datastep will now create a single table, called "Public Health", instead of two tables, one called "Public" and the other called "Health". But you will then have to refer to it as "Public Health"n.
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.