topic Splitting dataset into multiple datasets in SAS Procedures

Splitting dataset into multiple datasets

Sledgehamma — Sun, 21 Jul 2019 10:49:04 GMT

I need to split my dataset into several datasets based on a certain variable. Searching the forums I found this old thread which almost does the exact same:

https://communities.sas.com/t5/SAS-Procedures/Splitting-a-dataset-into-multiple-dataset/m-p/399904#M66679

The difference is that in this thread the procedure is done by looking at one variable to split the dataset and another variable for naming the datasets.

I’m still relatively new to SAS so I don’t fully understand what the code does.

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

In my case the data would look something like this:

(I changed the Input Statements for this example accordinlgy I hope. The actual data I'm working with is a SAS dataset)

data have;
infile datalines truncover;
input @1 dept $1-13 Variable1  4.;
datalines;
Maths 213
Astronomy 34
Maths 43
Physics 34
Public Health 54
Physics 324
;
run;

I think I Need to change These two lines:

call symputx('subj'|| left(put(i,2.)),deptdescription);

where deptdescription="&&subj&i";

However, I tried several changes such as:

call symputx('subj'|| compress(dept),Variable1);

plus

where variable1="&&subj&i";

But they didn’t work. Note that compress is not an elegant solution, but I needed a quick way to get rid of the space in the dataset Name.

Re: Splitting dataset into multiple datasets

Astounding — Mon, 22 Jul 2019 03:22:00 GMT

I too hope that the actual data you work with is a SAS data set. The INPUT statements you are creating are not going to work.

There are a handful of changes to make. One approach would be to use an additional macro variable. You now have (and should keep):

call symputx('subj'|| left(put(i,2.)),deptdescription);

Add another macro variable at that point in the program:

call symputx('dsname'|| left(put(i,2.)),compress(deptdescription));

Then change the macro loop so it refers to both variables:

%macro test;
%do i=1 %to &total;
data &&dsname&i;
set have;
where deptdescription="&&subj&i";
run;
%end;
%mend test;

Let me know if you run into any difficulties with that.

As a general rule, it's a good idea to avoid macro language when you are just starting to learn SAS.

Re: Splitting dataset into multiple datasets

Sledgehamma — Mon, 22 Jul 2019 05:42:13 GMT

Thank you very much!

I changed the code accordingly but I’m getting the following errors:

WARNING: Apparent symbolic reference TOTAL not resolved.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:

&total

ERROR: The %TO value of the %DO I loop is invalid.

ERROR: The macro TEST will stop executing.

Re: Splitting dataset into multiple datasets

Astounding — Mon, 22 Jul 2019 09:04:49 GMT

Apparently you are not creating &TOTAL. Show the final DATA step (before running the %DO loop).

Re: Splitting dataset into multiple datasets

Sledgehamma — Tue, 23 Jul 2019 05:31:55 GMT

I was just about to copy the data step when I noticed that I hadn’t changed two variables….they were still called „deptdescription“ from the early example. No wonder why it didn’t work.

The error wasn’t in the program but in front of the computer. Thanks again for your help, its much appreciated!