Macro variable creation based on date value in SAS

Question

I have a list of company IDs and Dates and I want to run a macro on this list in such a way that for each date all the company IDs need to be considered as my macro filter.

For example, my list is -

 DATA comp_date_list;
   INPUT compno sdate;
   DATALINES;
    12490 20090120
    87432 20090120
    24643 20090120
    87432 20090119
    12490 20090105
    24643 20090105
    ;
    proc print data=comp_date_list;
    run;

Now, I have a macro that is as follows -

%macro1(compno=,sdate=,threshold=,fdate=,edate=)

Now The macro has to run for every comp-date combination in my list. But since this is to run on a very large dataset, running it n times will take a long time. So to reduce the runtime, I plan to make a list of compnos for a given date and alter my macro to produce the results for a date.

Now my question is how to create a macro variable that has all the compnos for a given date and which alters as date changes? am new to macro writing and SAS. So please excuse my ignorance. Thanks!

Using by processing is usually far more efficient than using a macro to iterate over all your values/parameters, providing you can construct your code appropriately. Can you provide an example of the macro1 code? — Chris J
macro1 code is a large code that calculates some values for a large financial dataset. And my boss doesn't want to alter that section of the code. — RHelp

rambles rambles · Accepted Answer · 2014-04-04T12:16:00

A call execute statement in a datastep can run selective code (in this case, your macro) after the datastep in which it was called. For example, the following should work for you:-

proc sort data = comp_date_list;
  by sdate compno;
data _null_;
  set comp_date_list;
  by sdate;

  attrib all_comps_on_date length=$1000 label="All_comps_on_date: '|' separated company numbers for date";
  retain all_comps_on_date "";

  if first.sdate then all_comps_on_date = '';
  all_comps_on_date = catx('|', all_comps_on_date, compno);
  if last.sdate then call execute('%macro1(compno='||strip(all_comps_on_date)||',sdate=,threshold=,fdate=,edate=)');
run;

A note of caution though; call execute can play havoc with macros that themselves create macro variables (especially if they are using call execute statements!)

I can only echo @ChrisJ and add that while SAS macros can be useful, maintaining and debugging them is a pain and I only use them as a last resort. Not much help of course with legacy code!

Macro variable creation based on date value in SAS

1 Answers