HI - I am using SAS EG 5.1 in unix platform.
I have a dataset as below, the values in each column may change timely. I would want to create macro variables dynamically by reading the dataset.
I would need output as below,
macrovariable name macro value
ID_CRD_ORG_CDE ('001','005')
ID_RC_ORG_CDE ('000')
ID_BZ11_ORG_CDE ('XXX')
I have tried in many ways not getting exact values.
Please help me on this.
Thank You,
Durga.
Well, my first question is why do you want to do this? There are several things here, is that dataset structure fixed, will there always be those columns, will there only ever be three rows? If not, how many macro variables are you looking at here? Personally, I don't feel going down that route is the best way to go, from my side in 95% of cases there are better methods of doing things if you find your creating lots of macro variables/macro code to do something, but without knowing what your trying to do I can't say. As for code, what do you want the macro variables to be called?, in theory you could do something like - and again, it comes down to poor process - this, but then you need to find out how many macro variables are created/what they are called etc. and your code starts to get very complicated very quickly - so this is not recommended:
data your_dataset;
id_crd_org_cde="001"; id_rc_org_cde=""; id_bz1_org_cde="XX"; output;
id_crd_org_cde="002"; id_rc_org_cde=""; id_bz1_org_cde="YY"; output;
run;
data _null_;
set your_dataset;
array var{3} id_crd_org_cde id_rc_org_cde id_bz1_org_cde;
do i=1 to 3;
mname=cats('var',put(i,1.),'_',put(_n_,1.));
if var{i} ne "" then call symput(mname,var{i});
end;
run;
%put &var1_1. &var1_2. &var3_1. &var3_2.;
However, again, I advise that you re-assess your approach as so much can go wrong with it.
Why do you need to get a list of variables separated by commas? The reason is that merging (or sub-qeurying) is far simpler to maintain. For instance:
proc sql; select VALUES from MYDATA where VALUES in (select distinct CR_ID_ORG_CDE from CODELIST); quit;
AS you can see, in this example I sub-query the codelist dataset in a where step. You could also join (SQL) or merge (datastep) the codelist on and only keep where it exists in the code list dataset. Again, in 99.9% of instances you can achieve your goal by using base SAS, no need or reason to use macro language, which just leads to complicated hard to read code.
Just load your parameters to a temporary table in the work area on the database:
proc sql;
connect to db (...);
execute('create table TEMP$TEMP_DS as select * from WORK.YOUR_DS') by db;
disconnect from db;
quit;
You can then reference that table in your passed through query.
Just seconding @RW9 "from my side in 95% of cases there are better methods"
From my side that's something like 99.9%
Explain us your problem, what you have and what you want to achieve. I'm almost 100% sure that there is a "better" way of getting there.
It looks like you are trying to construct a list of values, to compare using IN. If so, you don't need the parentheses added up front.. It would be more flexible to omit them and later use:
if var in (&id_crd_org_cde);
At any rate, here's an easy method.
proc sql noprint;
select distinct "'" || strip(id_crd_org_cde) || "'" into id_crd_org_cde separated by ',' where id_crd_org_cde > ' ';
quit;
You would need similar SELECT statements for the other two variables. One piece that is not clear is the source for the last macro variable. Does it come from a single variable, or does it come from three variables?
Good luck.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.