topic Multiple macro variable with condition in SAS Programming

Multiple macro variable with condition

umashankersaini — Wed, 08 Jul 2015 17:34:10 GMT

Hi All,

I am reading a dataset and creating macro variable with respect to observation (specific) value. Created macro variable would be used in another code. Currently i am writing in following way (for example) :

Data DSN;

input X $17. @18 Y $15. ;

Cards;

Value of PROJECT Most_valuable

Trans of Money Already done

Cost to company Disclosed

Company to Cost Paid

Run;

options symbolgen mprint;

Data _NULL_;

Set DSN;

If trim(upcase(X))='VALUE OF PROJECT' Then call symput ("PROJECT",(Y));

Run;

Data _NULL_ ;

Set DSN;

if trim(upcase(X))='TRANS OF MONEY' then call symput ("Money",(Y));

Run;

and so on.. I have approx 50 observation to create macro variable. Please help me to write that in PROC SQL with specified condition.

Log should show something for this :

%Put &Project &Money;

SYMBOLGEN: Macro variable PROJECT resolves to Most_valuable

SYMBOLGEN: Macro variable MONEY resolves to Already done.

Please help.

Thanks in advance

Umae

Re: Multiple macro variable with condition

Haikuo — Wed, 08 Jul 2015 18:03:22 GMT

May I ask why? the following will NOT be the same if the condition is not unique. It will take the first one while your data step takes the last one that meets your condition. It can be tweaked, but before I know your whole picture, it may not worth the extra mileage.

proc sql;

select y into :project from dsn where trim(upcase(X))='VALUE OF PROJECT' ;

select y into :money from dsn where trim(upcase(X))='TRANS OF MONEY' ;

quit;

Re: Multiple macro variable with condition

Reeza — Wed, 08 Jul 2015 18:13:37 GMT

That doesn't make a huge amount of sense to me.

If you know a variable maps to something you want to create a macro variable for make a master list somewhere.

Untested and can probably be made more efficient but hopefully points you in the right direction. The alternative is a table similar to my proc format, join via sql and then a similar Call symputx statement.

proc format;

invalue $ mv_fmt

'TRANS OF MONEY' = 'MONEY'

"VALUE OF PROJECT' = 'PROJECT';

RUN;

data _null_;

set have ;

if trim(upcase(x)) in ('TRANS OF MONEY', 'VALUE OF PROJECT') THEN DO;

call symputx(input(trim(upcase(x)), y);

end;

run;

Re: Multiple macro variable with condition

Tom — Wed, 08 Jul 2015 18:14:13 GMT

Why not make a format or informat to translate the value of X to the macro variable name?

Here is example using an INFORMAT.

proc format ;

invalue $mvarname (upcase just)

'VALUE OF PROJECT'='PROJECT'

'TRANS OF MONEY'='MONEY'

'COST TO COMPANY'='COMPANY'

'COMPANY TO COST'='COST'

;

run;

data DSN;

input X $17. @18 Y $15. @1 mvarname $mvarname17. ;

cards;

Value of PROJECT Most_valuable

Trans of Money Already done

Cost to company Disclosed

Company to Cost Paid

run;

proc print; run;

data _null_;

set dsn ;

call symputx(mvarname,y);

run;

%put _user_;