Creating SAS macro variable using Dataset

[Mani1]

Hi,

I'm working on creating macro variable and to use that macro variable as filters in do loop statement to run code for different criteria.

here is the  reference code I'm using this for creating filter criteria for single variable (variable name is dn)

### macro to create filter criteria for dn

proc sql noprint;

select Strip(put(count(distinct dn),15.)) into :varCount from work.input_dsntm;

select distinct dn into :varVal1- :varVal&varCount from work.input_dsntm;

Quit;

%Macro RA;

%do index =1 %to &varCount;

PROC SQL;

SYSECHO "Getting data for &&varVal&index.";

CREATE TABLE WORK.ABC_DN AS

SELECT t1.ID,

t1.lt,

t1.cn

FROM WORK.master_data t1

WHERE t1.dn ="&&varVal&index.";

Run;

%end;

%mend;

but I'm i scenario to create group of filters to pull data for that criteria, like in below sample data code, for DN = 'CH', FC = 'ABC1' , SFC = 'ABC1234' and PN = 'ch1234', get data from master table. I have multiple line to repeat this process. so i was thinking to create macro variable to store these condition and then call into my macro program to get data 

(To be more clear in the above macro, for where statement apart from dn variable, I need to filter with FC, SFC, and PN along with dn.... I'm not finding way to do it. I got dataset below where every individual row, represent filter criteria which i need to use as filter to pull data from master_data set.)

data codes;

length DN $4 FC $50 SFC $50 PN $50;

DN = 'CH'; FC = 'ABC1'; SFC = 'ABC1234'; PN = 'ch1234'; output;

DN = 'CH'; FC = 'ABC1'; SFC = 'ABC1234'; PN = 'ch5678'; output;

DN = 'CH'; FC = 'ABC2'; SFC = 'ABC4567'; PN = 'ch1234'; output;

DN = 'CH'; FC = 'ABC2'; SFC = 'ABC123'; PN = 'ch6789'; output;

DN = 'HY'; FC = '123ABC'; SFC = '1ABC123'; PN = 'hy1234'; output;

DN = 'HY'; FC = '123ABC'; SFC = '1ABC123'; PN = 'hy1235'; output;

DN = 'HY'; FC = '456ABC'; SFC = 'ABC123'; PN = 'hy234'; output;

DN = 'HY'; FC = '456ABC'; SFC = '1ABC123'; PN = 'hy1236'; output;

DN = 'WH'; FC = 'ABCXYZ'; SFC = 'XYZ123'; PN = 'wh1234'; output;

DN = 'WH'; FC = 'ABCXYZ'; SFC = 'XYZ123'; PN = 'wh1235'; output;

DN = 'WH'; FC = 'ABCQWE'; SFC = 'ABC456'; PN = 'wh1238'; output;

DN = 'WH'; FC = 'ABCQWE'; SFC = 'ABC456'; PN = 'wh1239'; output;

run;

Looking for your help

Mani

[Reeza]

Assumption : that you're trying to split your data set into multiple smaller subsets for processing based on the variables listed. 

Any reason a join wouldn't work? In general, splitting data sets isn't considered efficient in SAS, unless you're having massive space issues.

Here's some references that may help:

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

---

@Mani1 wrote:

Hi,

I'm working on creating macro variable and to use that macro variable as filters in do loop statement to run code for different criteria.

here is the  reference code I'm using this for creating filter criteria for single variable (variable name is dn)

### macro to create filter criteria for dn

proc sql noprint;

select Strip(put(count(distinct dn),15.)) into :varCount from work.input_dsntm;

select distinct dn into :varVal1- :varVal&varCount from work.input_dsntm;

Quit;

%Macro RA;

%do index =1 %to &varCount;

PROC SQL;

SYSECHO "Getting data for &&varVal&index.";

CREATE TABLE WORK.ABC_DN AS

SELECT t1.ID,

t1.lt,

t1.cn

FROM WORK.master_data t1

WHERE t1.dn ="&&varVal&index.";

Run;

%end;

%mend;

but I'm i scenario to create group of filters to pull data for that criteria, like in below sample data code, for DN = 'CH', FC = 'ABC1' , SFC = 'ABC1234' and PN = 'ch1234', get data from master table. I have multiple line to repeat this process. so i was thinking to create macro variable to store these condition and then call into my macro program to get data 

(To be more clear in the above macro, for where statement apart from dn variable, I need to filter with FC, SFC, and PN along with dn.... I'm not finding way to do it. I got dataset below where every individual row, represent filter criteria which i need to use as filter to pull data from master_data set.)

data codes;

length DN $4 FC $50 SFC $50 PN $50;

DN = 'CH'; FC = 'ABC1'; SFC = 'ABC1234'; PN = 'ch1234'; output;

DN = 'CH'; FC = 'ABC1'; SFC = 'ABC1234'; PN = 'ch5678'; output;

DN = 'CH'; FC = 'ABC2'; SFC = 'ABC4567'; PN = 'ch1234'; output;

DN = 'CH'; FC = 'ABC2'; SFC = 'ABC123'; PN = 'ch6789'; output;

DN = 'HY'; FC = '123ABC'; SFC = '1ABC123'; PN = 'hy1234'; output;

DN = 'HY'; FC = '123ABC'; SFC = '1ABC123'; PN = 'hy1235'; output;

DN = 'HY'; FC = '456ABC'; SFC = 'ABC123'; PN = 'hy234'; output;

DN = 'HY'; FC = '456ABC'; SFC = '1ABC123'; PN = 'hy1236'; output;

DN = 'WH'; FC = 'ABCXYZ'; SFC = 'XYZ123'; PN = 'wh1234'; output;

DN = 'WH'; FC = 'ABCXYZ'; SFC = 'XYZ123'; PN = 'wh1235'; output;

DN = 'WH'; FC = 'ABCQWE'; SFC = 'ABC456'; PN = 'wh1238'; output;

DN = 'WH'; FC = 'ABCQWE'; SFC = 'ABC456'; PN = 'wh1239'; output;

run;

Looking for your help

Mani

---

[Tom]

Is there a question here?  If so what is it?

Note there is no need to run your SQL query twice to count how many records it returns. SQL will set SQLOBS to that count for you.

proc sql noprint;
select distinct dn 
  into :varVal1- 
  from work.input_dsntm
;
%let varCount=&sqlobs;
quit;

[Mani1]

I have a data set, from which I need to get new data with multiple variable filter criteria. the code I have shown in my question is what I use to get data using one variable filter. Now i'm in need to uses multiple variable filter as below. 

DN = 'CH'; FC = 'ABC1'; SFC = 'ABC1234'; PN = 'ch1234'

I need to execute this code for multiple set of criteria with DN, FC, SFC, PN …..which is there in separate dataset. So i'm using macro program to do this task.

[Reeza]

Ok, so what's the question for us?
Is your code not working as expected? Do you not know how to extend it for multiple variables or tables?
Or are you looking for suggestions to improve this?

I would highly recommend following the second tutorial in my first response. I think you want a data driven approach so creating a small macro and using CALL EXECUTE are a good option. You could do it all from a data step and call execute.

You have not specified what you need as output though. Currently your output table is ABC_DN which gets replaced each time the macro loops.
Did you mean to have your output table be:

ABC_&DN. so it is dynamic?

[Mani1]

I don’t know how to do it for multiple variable. I was able to do it for single variable.

I got dataset, each row combination represent filter criteria... I don’t to how to convert this to macro variable with each unique row in the dataset and use it in my macro do loop function.

[Reeza]

Then follow the tutorial I linked to. First provide working code that has no macro variables that does what you want and then you convert that to a macro. This is the best, fastest and most error proof method to write a macro.

[Mani1]

I don’t know how to do it for multiple variable. I was able to do it for single variable.

I got dataset, each row combination represent filter criteria... I don’t to how to convert this to macro variable with each unique row in the dataset and use it in my macro do loop function.

[Reeza]

%macro demo(DN=, FC=, SFC=, PN=, dsn=);

CREATE TABLE WORK.&dsn AS

SELECT t1.ID,

t1.lt,

t1.cn

FROM WORK.master_data t1

WHERE t1.DN= "&DN" 
& t1.FC = "&FC"
& t1.SFC = "&SFC"
& t1.PN = "&PN";

%mend;

data execute_code;

set codes;

str = catt('%demo(DN='
, DN, 
", FC = ",
 FC,
 " , SFC=", 
SFC, 
" , PN = ",
PN,
", DSN= ",
DSN,
");"
);

*call execute(str);

run;

Make sure the STR variable looks like this first:

%demo(DN=CH, FC=ABC1, SFC=ABC1234, PN=ch1234, dsn=DATA1);

1. Add a variable DSN to your CODES data set, this will control thename of the ouptut data set

2. Verify the macro demo works using a %demo call 

3. Try the data step, making sure the STR variable is created properly. 

4. Once that's confirmed, uncomment the CALL EXECUTE portion and it will call your macro for each line in the CODES data set. 

---

@Mani1 wrote:

I don’t know how to do it for multiple variable. I was able to do it for single variable.

I got dataset, each row combination represent filter criteria... I don’t to how to convert this to macro variable with each unique row in the dataset and use it in my macro do loop function.

---

[Tom]

Still not that clear.  Is the data you are showing the set of CRITERIA to use to query some other dataset?  Or the data to be queried?

If the data is the criteria then perhaps you just want to generate different macro variables for each actual variable?

If you want to make the same style of a series of macro variables you could just use the data step iteration number to help generate the macro variable name.

data _null_;
  set values_list ;
  call symputx(cats('dn',_n_),dn);
  call symputx(cats('fc',_n_),fc);
  call symputx(cats('sfc',_n_),sfc);
  call symputx(cats('pn',_n_),pn);
run;

Put it is probably going to be easier eliminate the series of macro variables and the complexity it requires and instead make a macro that takes the four values as inputs.

%macro run_one(dn,fc,sfc,pn);
....
%mend;

And then call it from the list of values dataset.

data _null_;
 set list_of_values;
 call execute(cats('%nrstr(%run_one)(',dn,',',fc,',',sfc,',',pn,')'));
run;

[Mani1]

Is the data you are showing the set of CRITERIA to use to query some other dataset?  Or the data to be queried?

the data I'm showing is the list of unique filter combination. (Below is an example). Here is how criteria dataset looks like. This is used to query some other dataset. (using where statement, each row combination in the below dataset will be the filter criteria... in same combination)

data codes;

length DN $4 FC $50 SFC $50 PN $50;

DN = 'CH'; FC = 'ABC1'; SFC = 'ABC1234'; PN = 'ch1234'; output;

DN = 'CH'; FC = 'ABC1'; SFC = 'ABC1234'; PN = 'ch5678'; output;

DN = 'CH'; FC = 'ABC2'; SFC = 'ABC4567'; PN = 'ch1234'; output;

DN = 'CH'; FC = 'ABC2'; SFC = 'ABC123'; PN = 'ch6789'; output;

DN = 'HY'; FC = '123ABC'; SFC = '1ABC123'; PN = 'hy1234'; output;

DN = 'HY'; FC = '123ABC'; SFC = '1ABC123'; PN = 'hy1235'; output;

DN = 'HY'; FC = '456ABC'; SFC = 'ABC123'; PN = 'hy234'; output;

DN = 'HY'; FC = '456ABC'; SFC = '1ABC123'; PN = 'hy1236'; output;

DN = 'WH'; FC = 'ABCXYZ'; SFC = 'XYZ123'; PN = 'wh1234'; output;

DN = 'WH'; FC = 'ABCXYZ'; SFC = 'XYZ123'; PN = 'wh1235'; output;

DN = 'WH'; FC = 'ABCQWE'; SFC = 'ABC456'; PN = 'wh1238'; output;

DN = 'WH'; FC = 'ABCQWE'; SFC = 'ABC456'; PN = 'wh1239'; output;

run;

if the data is the criteria then perhaps you just want to generate different macro variables for each actual variable?

yes, I want to generate macro variable which want to use in below do loop macro. Below macro do loop is what I have created for one variable - dn. I want to filter for dn, fc, sfc and pn (multiple variable as macro.

%Macro RA;

%do index =1 %to &varCount;

PROC SQL;

SYSECHO "Getting data for &&varVal&index.";

CREATE TABLE WORK.ABC_DN AS

SELECT t1.ID,

t1.lt,

t1.cn

FROM WORK.master_data t1

WHERE t1.dn ="&&varVal&index.";

Run;

%end;

%mend;

This is the statement I used to get all value in one variable to pass into above macro loop

proc sql noprint;

select Strip(put(count(distinct dn),15.)) into :varCount from work.input_dsntm;

select distinct dn into :varVal1- :varVal&varCount from work.input_dsntm;

Quit;

Other info: each time I run macro do loop, I use the output dataset for some analysis, result of which I record in seperate dataset.

Hope this is clear, sorry for confusion.

[Tom · Accepted solution]

This is what is sounds like to me.

You probably do NOT want to put the values of DN and SN into the same macro variable named VARVAL1.

Instead you should just make a different macro variable for each source REAL variable.  It is probably easiest if you just name the macro variables using the same name as the real variables.

proc sql noprint;
select distinct
       dn
     , fc
     , sfc
     , pn
  into :dn1-
     , :fc1-
     , :sfc1-
     , :pn1-
from work.input_dsntm
;
%let varCount=&sqlobs;
quit;

Then in the body of your code you can use the same pattern to find the right macro variable.

where t1.dn = "&&DN&index."
  and t1.fc = "&&FC&index."
  and t1.sfc = "&&SFC&index."
  and t1.pn = "&&PN&index."

[Mani1]

Thank you Tom and Reeza for your support.

Tom,  in the code you have provided below,  please advice if it read each row in my dataset and assign values I described in below example.

select distinct
       dn
     , fc
     , sfc
     , pn
  into :dn1-
     , :fc1-
     , :sfc1-
     , :pn1-

For example

for the below dataset, when I use above code,

dn1 will be CH, fc1 will be 'ABC1' sfc1 will be 'ABC1234 and pn1 will be 'ch1234, and same way

dn2 will be CH, fc2 will be ABC1, sfc2 will be 'ABC1234' and pn2 will be 'ch5678, and so on.... please confirm. I will try this.

DN = 'CH'; FC = 'ABC1'; SFC = 'ABC1234'; PN = 'ch1234'; output;

DN = 'CH'; FC = 'ABC1'; SFC = 'ABC1234'; PN = 'ch5678'; output;

DN = 'CH'; FC = 'ABC2'; SFC = 'ABC4567'; PN = 'ch1234'; output;

DN = 'CH'; FC = 'ABC2'; SFC = 'ABC123'; PN = 'ch6789'; output;

DN = 'HY'; FC = '123ABC'; SFC = '1ABC123'; PN = 'hy1234'; output;

DN = 'HY'; FC = '123ABC'; SFC = '1ABC123'; PN = 'hy1235'; output;

Thanks for your support

Mani.

[Tom]

Why not just try it and see?

[Reeza]

So Tom's approach and my approach are different. You need to pick one and go with that. I'm not a fan of macro loops as they're harder to debug, and if you have the data in a data set, a data driven approach works for me. Ultimately, it's your choice. You can always try both to see the differences, but please be clear in your responses in which approach you're trying so that we can respond appropriately.