How can I perform a macro for each observation in sas data set?

Question

Here is the macro code.....

libname myfmt "&FBRMrootPath./Formats";
%macro CreateFormat(DSN,Label,Start,fmtname,type);
options mprint mlogic symbolgen;
%If &type='n' %then %do;
    proc sort data=&DSN out=Out; by &Label;
        Run;
    Data ctrl;
        set Out(rename=(&Label=label &Start=start )) end=last;
        retain fmtname &fmtname type &type;
        output;
    If last then do;
        hlo='O';
        label='*ERROR';
        output;
    End;
Run;
%End;
%Else  %do;
    proc sort data=&DSN out=Out; by &Start;
        Run;
    Data ctrl;
        set Out(rename=(&Start=label &Label=start )) end=last;
        retain fmtname &fmtname type &type;
        output;
    If last then do;
        hlo='O';
        label='*ERROR';
        output;
    End;
Run;
%End;
proc format library=myfmt cntlin=ctrl;
Run;
%Mend CreateFormat;

Here is the code for control data set through which above macro should run for each observation of the data set and the values of the observations are inputs for varibales in the macro....

Data OPER.format_control;
Input DSN :$12.  Label :$15. Start :$15. fmtName :$8. type :$1. fmt_Startdt :mmddyy. fmt_Enddt :mmddyy.;
format fmt_Startdt fmt_Enddt date9.;
Datalines;
ssin.prd prd_nm prd_id mealnm n . 12/31/9999
ssin.prd prd_id prd_nm mealid c . 12/31/9999
ssin.fac fac_nm onesrc_fac_id fac1SRnm n . 12/31/9999
ssin.fac fac_nm D3_fac_id facD3nm n . 12/31/9999
ssin.fac onesrc_fac_id D3_fac_id facD31SR n . 12/31/9999
oper.wrkgrp wrkgrp_nm wrkgrp_id grpnm n . 12/31/9999
;

score 0 · Answer 1 · answered Feb 12 '13 at 20:59

Something like this.

proc sql;
select catx(',',cats('%CreateFormat(',DSN),Label,Start,fmtname,cats(type,')');
into :formcreatelist separated by ' '
from oper.format_control;
quit;

You may need to PUT some of your variables to get the format you want into the macro variable. I use the slightly cludgy cats/catx combo here, you could cats once with ',' added in a bunch of times also.

You do have a limit here - around 20,000 characters total in a macro variable. If it's over that, you either have to use CALL EXECUTE (which has some quirky features) or you can put the macro call into a text file and %INCLUDE it.

score 0 · Answer 2 · answered Sep 02 '13 at 20:42

There is a better way to do this rather than select ... into a macro variable. Use a temp file like this:

filename dyncode temp;

data _null_;
   file dyncode;
   set OPER.format_control;
   put '%createformat ....';
run;

%include dyncode;

filename dyncode clear;

This technique is not limited by the 32k length limitation on macro variables.

Note that you should definitely use single quotes around the %createformat to prevent SAS from invoking the macro just prior to data step compilation. You want the macro to run when the %include runs.

The above approach is analogous to call execute, but call execute is evil because it does not execute the macro and embedded data/proc code within the macro in the expected order. Avoid call execute.

Finally, if you are running interactive SAS and using the technique there is a neat trick you can use to debug. Comment out the last two lines of code -- the include and the filename clear. After you run the remaining code, enter the SAS command "fslist dyncode" in the command window. This will pop up a notepad view on the dynamic code you just generated. You can review it and make sure you got what you intended.

I've posted a sample call execute solution. Would this run the iterations of the macro in a different order from your solution? — user667489, Dec 15 '13 at 00:02
call execute will run in the macro steps in the correct order in your solution. So no worry there. However, the simple fact that call execute will *sometimes* run in an unexpected order is scary to me and I now avoid it purely on principal. Here is a paper that discusses this issue: http://www2.sas.com/proceedings/sugi22/CODERS/PAPER70.PDF See section "Example Invoking Macro" on page 2/3. — John Leveille, Jul 01 '14 at 17:50

score 0 · Answer 3 · answered Dec 15 '13 at 00:00

0

Here's a call execute solution, just for completeness:

data _null_;
  set OPER.format_control;
  call execute('%CreateFormat(' || DSN || ',' || Label || ',' || Start || ',' || fmtname || ',' || type || ');');
run;

answered Dec 15 '13 at 00:00

user667489

9,501
2
24
35

How can I perform a macro for each observation in sas data set?

3 Answers3