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 | … | … | … |
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
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
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.
%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.
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
There are lots of way to do that. But I prefer to Hash Table.
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).
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;
Many thanks, I am keeping this for future reference. Am sure it will come in handy.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.