Desktop productivity for business analysts and programmers

create macro variables dynamically from dataset

Reply
Occasional Contributor
Posts: 13

create macro variables dynamically from dataset

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.

 

Capture.PNG

 

 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.

Super User
Super User
Posts: 7,711

Re: create macro variables dynamically from dataset

[ Edited ]

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. 

Occasional Contributor
Posts: 13

Re: create macro variables dynamically from dataset

Thanks for your response.

my dataset variables are not fixed. we need to pick variables dynamically, Today it is 3 going forward it can be more than that. But my code need to read all existing fields and create the macro variable with the values as showed in the example(comma separated and within brackets).

I hope this explanation gives clarity.

Super User
Super User
Posts: 7,711

Re: create macro variables dynamically from dataset

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.

Occasional Contributor
Posts: 13

Re: create macro variables dynamically from dataset

I understand what you saying.
here is my requirement:
I need to have list of values which can be used in other pass though queries. macro variables may need to use in 'select case' statement, where , group by, having clause etc.
Since, I have values in local sas dataset and I have to use pass though facility for connecting DB, I am thinking to prepare macro variables which can be used either of above mentioned clauses.

Please suggest me if there is any better way.

Super User
Super User
Posts: 7,711

Re: create macro variables dynamically from dataset

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.  

Respected Advisor
Posts: 4,132

Re: create macro variables dynamically from dataset

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. 

Super User
Posts: 5,362

Re: create macro variables dynamically from dataset

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.

Ask a Question
Discussion stats
  • 7 replies
  • 517 views
  • 0 likes
  • 4 in conversation