topic Re: Creating SAS macro variable using Dataset in SAS Programming

Creating SAS macro variable using Dataset

Mani1 — Mon, 30 Sep 2019 14:15:30 GMT

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

Re: Creating SAS macro variable using Dataset

Reeza — Mon, 30 Sep 2019 15:00:07 GMT

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-Appendix/ta-p/291716

@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

Re: Creating SAS macro variable using Dataset

Tom — Mon, 30 Sep 2019 15:08:59 GMT

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;

Re: Creating SAS macro variable using Dataset

ballardw — Mon, 30 Sep 2019 17:40:32 GMT

I think adding macro variables is just going to make things harder to maintain or understand.

If I understand your goal then something more like

proc sql;
    create table want as
    select <variable list>
    from dataset
    where dn in ('CH' 'HY' 'WH')
       and FC in ('ABC1' 'ABC2' '123ABC' '456ABC' 'ABCXYZ' 'ABCQWE')
       and SFC in ('ABC1234' 'ABC4567' )
       and PN  in ('ch1234' 'ch5678')
   ;
quit;

I didn't put your full list of values for SFC and PN but the pattern should be clear. ( and they appear to be dummy values as well)

Re: Creating SAS macro variable using Dataset

Mani1 — Mon, 30 Sep 2019 16:28:34 GMT

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.

Re: Creating SAS macro variable using Dataset

Reeza — Mon, 30 Sep 2019 16:38:37 GMT

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?

Re: Creating SAS macro variable using Dataset

Mani1 — Mon, 30 Sep 2019 16:43:32 GMT

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.

Re: Creating SAS macro variable using Dataset

Mani1 — Mon, 30 Sep 2019 16:44:19 GMT

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.

Re: Creating SAS macro variable using Dataset

Reeza — Mon, 30 Sep 2019 16:44:32 GMT

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.

Re: Creating SAS macro variable using Dataset

Tom — Mon, 30 Sep 2019 16:58:32 GMT

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;

Re: Creating SAS macro variable using Dataset

Mani1 — Mon, 30 Sep 2019 17:16:32 GMT

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)