data main ;
input id score ;
cards ;
101 200
102 355
;
proc sql ;
select score into:rate1 from main where id=101 ;
select score into:rate2 from main where id=102 ;
select score into:rate3 from main where id=103 ;
quit;
%put &rate1 &rate2 ;
data want ;
set main ;
var1=symgetn("rate1")*1 ;
var2=symgetn("rate2")*1.5 ;
var3=symgetn("rate3")*2 ;
run;
I have one dataset like main in that only two id's there in future may be 3 id's there so I created 3 macro variables next I wrote
One calculation I got var1 ,var2 results however for var3 I am getting error log window how to ignore that error.
The SYMEXIST() function can be used to test if a macro variable exists.
You could also consider defining "rate3" as a null macro variable at an earlier step. This should resolve the issue. If ID 103 is available "rate3" will be reassigned at the PROC SQL step.
In this case you will get an empty VAR3 variable in data WANT even if ID 103 does not exist (not sure if you want that).
Note that you will need to check for missing rate3 value before deriving VAR3 to keep your code (completely) clean. Otherwise you will get a note for missing values.
/*Include this before proc sql step*/
%let rate3 = ;
/*update derivation of var3 to check for missing*/
if symgetn("rate3") ne . then var3=symgetn("rate3")*2 ;
The reason you are not seeing the macro variables is that the SELECT ... INTO ... process of SQL does NOT create macro variables when no observations are selected. The easiest solution is just to set the macro variables to some default value before running the query.
%let rate1=;
%let rate2=;
%let rate3=;
proc sql NOPRINT;
select score into :rate1-
from main
where id in (101:103)
order by id
;
quit;
But it might just be better to not use macro variables at all. Instead keep the values in dataset variables instead.
proc transpose data=main out=rates(drop=_name_) prefix=var ;
var score ;
run;
data want ;
set main ;
if _n_=1 then set rates;
run;
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.