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
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;
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;
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_;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.