topic Re: split a data set into multiple data sets using macro in SAS Programming

split a data set into multiple data sets using macro

Ronein — Wed, 17 Apr 2019 11:28:48 GMT

Hello

Let's say that I want to split data set sashelp.cars into multiple datasets by the value of field "Length".

I expect to get 67 data sets because there are 67 distinct values of field "Length".

for example:

Name of data set for rows with Length=143 should be tbl_143

Name of data set for rows with Length=169 should be tbl_169

What is the clever way to do it please?

Re: split a data set into multiple data sets using macro

gamotte — Wed, 17 Apr 2019 11:56:13 GMT

Hello,

I would say that the clever way to do it is not to do it and rather use by group processing.

proc sql;
    CREATE TABLE cars AS
    SELECT DISTINCT length
    FROM sashelp.cars;
quit;

data _NULL_;
    set cars;
    call execute(cats('data tbl_',length,'; set sashelp.cars; where length=',length,'; run;'));
run;

Re: split a data set into multiple data sets using macro

PeterClemmensen — Wed, 17 Apr 2019 12:38:02 GMT

@Reeza wrote a macro for this exact purpose.

https://communities.sas.com/t5/SAS-Communities-Library/Splitting-a-SAS-data-set/ta-p/517960

Re: split a data set into multiple data sets using macro

PaigeMiller — Wed, 17 Apr 2019 12:43:17 GMT

@gamotte wrote:

Hello,

I would say that the clever way to do it is not to do it and rather use by group processing.

Yes, indeed! Not splitting up data sets is the most clever thing to do, because it simplifies your programming and takes advantage of things SAS has already programmed for your benefit.