BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
stlimpbizkit
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

8 REPLIES 8
Cynthia_sas
SAS Super FREQ

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

stlimpbizkit
Calcite | Level 5

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.

Kurt_Bremser
Super User

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

jakarman
Barite | Level 11

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 --<-----
Ksharp
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

WUN
Calcite | Level 5 WUN
Calcite | Level 5

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;

stlimpbizkit
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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