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

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

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

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_nummonth_since_openedattribute1attribute2
18
28
38
48
57
67
76
86

Accepted Solutions
Solution
‎06-09-2015 11:42 PM
SAS Super FREQ
Posts: 8,719

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

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

View solution in original post


All Replies
Solution
‎06-09-2015 11:42 PM
SAS Super FREQ
Posts: 8,719

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

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

Contributor
Posts: 23

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

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.

Esteemed Advisor
Posts: 6,646

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 3,206

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

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

---->-- ja karman --<-----
Grand Advisor
Posts: 9,576

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

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

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

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

New Contributor WUN
New Contributor
Posts: 2

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

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 Smiley Very HappyATALINE separated by ' '
        ,Smiley FrustratedPLIT separated by ';'
  from ( select monotonic() as TABLE
               ,month
          from (select distinct month
                from SAMPLE))
;quit;

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

Contributor
Posts: 23

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 416 views
  • 7 likes
  • 7 in conversation