BookmarkSubscribeRSS Feed
Durga_SAS
Fluorite | Level 6

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.

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

Durga_SAS
Fluorite | Level 6
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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Durga_SAS
Fluorite | Level 6
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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

Patrick
Opal | Level 21

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. 

Astounding
PROC Star

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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3302 views
  • 1 like
  • 4 in conversation