topic Re: Is it possible to use macro to subset one input dataset into many sub-datasets? in SAS Programming

Is it possible to use macro to subset one input dataset into many sub-datasets?

stlimpbizkit — Tue, 09 Jun 2015 21:20:45 GMT

For example, if we were to subset the following table into 3 datasets based on month_since_opened value, as in: table 1 contains account 1-4; table 2 contains account 5-6; table 3 contains account 7-8.

I understand that It is fairly easy to write out the data steps without using macro based on the example below. However, when there are hundreds of different values under the field, the code can become too long and not efficient. Not sure if macro can be used in this manner, as to create datasets in bundle?

Acct_num	month_since_opened	attribute1	attribute2	…
1	8	…	…	…
2	8	…	…	…
3	8	…	…	…
4	8	…	…	…
5	7	…	…	…
6	7	…	…	…
7	6	…	…	…
8	6	…	…	…

Re: Is it possible to use macro to subset one input dataset into many sub-datasets?

Cynthia_sas — Wed, 10 Jun 2015 03:42:11 GMT

Hi:

This is certainly something you can do with Macro Language coding. You can also do it with Hash Tables.http://www2.sas.com/proceedings/forum2008/029-2008.pdf See example 5. You might have to change the logic because her examples selects just distinct values.

cynthia

Re: Is it possible to use macro to subset one input dataset into many sub-datasets?

Kurt_Bremser — Wed, 10 Jun 2015 06:14:43 GMT

%macro split_sets(dataset,splitvar);

proc sql noprint;

select distinct &splitvar into :value_list separated by " " from &dataset;

quit;

data

%let count=%sysfunc(countw(&value_list));

%do i = 1 %to &count;

%let value=%qscan(&value_list,&i);

output_&value

%end;

;

set &dataset;

select(&splitvar);

%do i = 1 %to &count;

%let value=%qscan(&value_list,&i);

when (&value) output output_&value;

%end;

end;

run;

%mend;

%split_sets(your_dataset_name,month_since_opened);

If you want the macro to be able to also deal with character variables for the splitting, you either have to add the quotes manually in the "when" or provide additional code to do this automatically depending on the attributes of &splitvar.

Re: Is it possible to use macro to subset one input dataset into many sub-datasets?

jakarman — Wed, 10 Jun 2015 06:48:17 GMT

There are variable lists in the Sas language that can make the coding more comfortable.

Developing a macro can be countereffective by introducing more problems than it is solving.

All depends on your issue you are having

Re: Is it possible to use macro to subset one input dataset into many sub-datasets?

Ksharp — Wed, 10 Jun 2015 14:33:34 GMT

There are lots of way to do that. But I prefer to Hash Table.

Re: Is it possible to use macro to subset one input dataset into many sub-datasets?

RW9 — Wed, 10 Jun 2015 15:24:10 GMT

Why does table one want to have four accounts when the other two only 2? If it was standard logic then:

proc sql;

select max(acct_num)

into :tot

from have;

quit;

data _null_;

do i=1 to &tot. by 2;

call execute('data want'||strip(put(i,best.))||'; set have (where=(acct_num in ('||strip(put(i,best.))||','||strip(put(i+1,best.))||'))); run;');

end;

run;

The above will generate a datastep for each two acct_num's (note, I assume it to be even number, you may need a bit more code if its uneven).

Re: Is it possible to use macro to subset one input dataset into many sub-datasets?

stlimpbizkit — Wed, 10 Jun 2015 15:54:41 GMT

Hash table does it. Thanks for the article - one of the example cases included speak directly what I am trying to solve. I am keeping the rest for future reference.

Re: Is it possible to use macro to subset one input dataset into many sub-datasets?

WUN — Wed, 10 Jun 2015 15:56:58 GMT

I hope you like the simplicity of this approach. The PROC SQL creates 2 macro variables that you can use in your datastep.

No Macro or Maco looping required. Take out the INTO section of the PROC SQL to see what its creating in the macro variable

data SAMPLE;
input month data; /* Stored a Number */
cards;
8 1111222233334444
2 2222333344445555
3 598758
5 598758321654
6 987654
8 598758
8 00005987581212
2 2371287598758222
;run;

proc sql;
select "DATASETNAME"!!put(TABLE,2.-L)
        ,"if MONTH = "!!put(MONTH,2.-L)!!" then output DATASETNAME"!!put(TABLE,2.-L)
    into :DATALINE separated by ' '
        ,:SPLIT separated by ';'
from ( select monotonic() as TABLE
               ,month
          from (select distinct month
                from SAMPLE))
;quit;

data &DATALINE;
set SAMPLE;
&SPLIT;
run;

Re: Is it possible to use macro to subset one input dataset into many sub-datasets?

stlimpbizkit — Tue, 16 Jun 2015 14:08:39 GMT

Many thanks, I am keeping this for future reference. Am sure it will come in handy.