SAS macro modification

Question

I have two values which represent dates:

 a=101 and b=103

Below is first macro saved in separate file one.sas:

 %global time nmall;
 %let nmall =;
 %macro pmall;
 %do i=&a. %to &b;
 %if &i =&a. then %do;
 %let nmall=&nmall.&i;
 %end;
 %else %let nmall=&nmall.,&i;
 end;
 %put (&nmall);
 %mend;

 %pmall;

So above pmall give me values 101,102,103.

Below is second macro:

%include “one.as”;

%macro c(a=,b=);
%let m=;
%let m1=;
%do i =&a %to &b;
%let o=&i;

proc sql;
 create table new&o as select * from data where nb in(&o.);quit;

%let m =&m.date&o;

data date&o.;
set date&o.;
if pass =&o.;
run;

proc sort data=date&o.;
by flag;
end;

data output &a._&b.;
set &m;
%mend;

The above macro creates three datasets date101 date102 and date 103, then append it to output101_103.

I am trying to modify above macros in such a way that I will not use %macro and %mend approach. Below is the modified macro code:

data a_to_c;
do o=&a to &c;
output;
end;
run;

so above code will have values 101 102 103 in variable o for dataset a_to_c.

 data _null_;
 set a_to_c;
 call execute ('create table new’||strip(o)||' as select * from data 
 where nb in(’||strip(o)||' );quit;’);
 run;

I want to know how to do below things.

1. Create pmall values in a macro variable in my modified macro inside the data step data a_to_c, so that I can use it further.

How to proceed from %let m macro in the first macro code to new code which I am developing above.

Answer 1

Pmall is macro variable which will have list of values separated by commas. In my modify macro, i want to create pmall as macro variable in the datastep data a_to_c; do o=&a to &c; output; end; run; – geetha anand 1 min ago

To create a macro variable from within a data step using the CALL SYMPUTX() function.

data a_to_c;
  length pmall $200 ;
  do o=&a to &c; 
    pmall=catx(',',pmall,o);
    output; 
  end; 
  call symputx('pmall',pmall);
  drop pmall;
run;

If you really want to generate code without a SAS macro you can use CALL EXECUTE() or write the code to a file and use %INCLUDE to run it. Or for small pieces of code you could try putting the code in a macro variable, but macro variables can only contain 64K bytes.

It is really hard to tell from what you posted what code you want to generate. Let's assume that you want to generate an new dataset for each value in the sequence and then append that to some aggregate dataset. So for the first pass through the loop your code might be as simple as these two steps. First to create the proper subset in the right order and the second to append the result to the aggregate dataset.

proc sort data=nb out=date101 ;
  where nb=101 ;
  by flag ;
run;

proc append base=date101_103 data=date101 force;
run;

Then next two times through the loop will look the same only the "101" will be replaced by the current value in the sequence.

So using CALL EXECUTE your program might look like:

%let a=101;
%let c=103;
proc delete data=date&a._&c ;
run;
data _null_;
  do nb=&a to &c; 
    call execute(catx(' ','proc sort data=nb out=',cats('date',nb,'),';'));
    call execute(cats('where nb=',nb,';')) ;
    call execute('by flag; run;');
    call execute("proc append base=date&a._&c data=");
    call execute(cats('date',nb));
    call execute(' force; run;');
  end;
run;

Writing it to a file to run via %INCLUDE would look like this:

filename code temp ;
data _null_;
  file code ;
  do nb=&a to &c; 
    put 'proc sort data=nb out=date' nb ';'
      / '  where ' nb= ';'
      / '  by flag;'
      / ';'
      / "proc append base=date&a._&c data=date" nb 'force;'
      / 'run;'
    ;
  end;
run;
proc delete data=date&a._&c ;
run;
%include code / source2;

If the goal is to just create the aggregate dataset and you do not need to keep the smaller intermediate datasets then you could just use the same name for the intermediate dataset on each pass through the loop. That will make the code generation easier as then there is only only place that needs to change based on the current value. Also that way you only need to have two dataset names even for a sequence of 10 or 20 values. It will take less space and reduce clutter in the work library.

Answer 2

Geetha:

I think you will find the macro-ization of the process to be far easier if you go from a data-centric explicit solution and proceed abstracting the salient features into macro symbols (aka variables)

The end run solution appears to be:

data output_101_to_103;
  set original_data;
  where nb between 101 and 103;
run;
proc sort data=output_101_to_103;
  by nb flag;
run;

In which case you could code a macro that abstracts 101 to FIRST and 103 to LAST. The data sets could also be abstracted. The abstracted parts are specified as the macro parameters.

%macro subsetter(DATA=, FIRST=, LAST, OUTPREFIX=OUTPUT);
  %local out;
  %let out = &OUTPREFIX._&FIRST._&LAST.;

  data &out;
    set &DATA.;
    where nb between &FIRST. and &LAST.;
    * condition = "between &FIRST. and &LAST."; * uncomment if you want to carry along the condition into your output data set;
  run;
  proc sort data=&out;
    by nb flag;
  run;
%mend;

And use as

%subsetter (data=original_data, first=101, last=103, outprefix=output)

Note: If you did keep the condition variable in the output data, you WOULD NOT be able to use it directly as a source code statement in a future data step, as in if nb condition then ...

I suppose you could also pass the NB and FLAG as parameters -- but you approach a point of diminishing returns on the utility of the macro.

Macro-izing the specific example I showed doesn't make too much sense unless you need to perform a lot of different variations of FIRST and LAST in a well documented framework. Sometimes it is just better to not abstract the code and work with the specific cases. Why? Because when there are too many abstracted pieces the macro invocation is almost as long as the specific code you are generating and the abstraction just gets in the way of understanding.

If the macro is simply chopping up data and reassembling data, you might be better served rethinking the flow using where, by, and class statements and abstracting around that.