topic Re: Creating a dynamic macro from proc freq chunk in SAS Programming

Creating a dynamic macro from proc freq chunk

adamqureshi_21 — Mon, 16 May 2022 18:33:33 GMT

Hello!

I have a dataset that contains the variable record_id. I am using the substr() function to filter by the first two numbers in this record_id variable and using a proc freq to create some tables. Example shown below:

proc freq data = import;
tables sex ethnicity age;
where substr(record_id,1,2)='14';
run;

What I would like to do is create a macro that changes the value of the substring to numbers between 13 - 19, as that encompasses all the prefixes for the record ID's. I'm completely inexperienced with macros so I'm not sure how to go about doing this, and I'm not sure whether to create a macro that encompasses the whole proc freq statement, or just the value of the substring. Any tips or help is appreciated.

Thanks!

Best,

Adam

Re: Creating a dynamic macro from proc freq chunk

PaigeMiller — Mon, 16 May 2022 18:53:57 GMT

How about this:

%macro dothis(string=);
proc freq data = import;
tables sex ethnicity age;
where substr(record_id,1,2)="&string";
run;
%mend;

Re: Creating a dynamic macro from proc freq chunk

adamqureshi_21 — Mon, 16 May 2022 19:03:23 GMT

That worked perfectly! It looks like I was just overthinking this. Thank you so much!

Best,
Adam

Re: Creating a dynamic macro from proc freq chunk

PaigeMiller — Mon, 16 May 2022 19:15:18 GMT

@adamqureshi_21

So, now I have to comment, I think you should do this without macros.

Example:

data intermediate;
    set import;
    prefix=substr(record_id,1,2);
run;
proc freq data=intermediate;
    tables prefix*(sex ethnicity age);
run;

Re: Creating a dynamic macro from proc freq chunk

adamqureshi_21 — Mon, 16 May 2022 20:02:30 GMT

Ah I see, that seems arguably easier. Thank you for this!

Re: Creating a dynamic macro from proc freq chunk

ballardw — Mon, 16 May 2022 23:21:25 GMT

Even shorter which takes advantage only wanting the first two characters. The format applied will have the output show just the first two characters. No need for additional variables or data set.

proc freq data=import;
    tables record_id*(sex ethnicity age);
    format record_id $2. ;
run;