topic Re: Macro using IF, THEN and ELSE, variable with in operator and do loop in SAS Programming

Macro using IF, THEN and ELSE, variable with in operator and do loop

shellp55 — Mon, 15 Jan 2018 23:25:55 GMT

Hello

I am using SAS 9.3.

I have a report where the sources for the same report may differ so I need to rename some fields. So if the variable field of inst_no includes those in the incl_list then rename using one field otherwise use another field. Below is wrong but I've been working on this forever so hopefully someone can help. Thanks.

%let inst_no=5555;
%let incl_list=3333, 4444, 5555, 6666;

%macro renm1;
%if &inst_no. in (&incl_list) %then %do I = 0 %to 20;
status&i=pxattr&i;
else %do i = 0 %to 20;
status&i=pxstat&i;
%end;
%mend;

Data have;
set mydata;
rename %renm1;
run;

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

novinosrin — Mon, 15 Jan 2018 23:31:08 GMT

You need the mindelimiter option in your macro statement. I hope I have corrected your code

%let inst_no=5555;
%let incl_list=3333, 4444, 5555, 6666;

%macro renm1/mindelimiter=',';
%if &inst_no. in (&incl_list) %then %do I = 0 %to 20;
status&i=pxattr&i;
%else %do i = 0 %to 20;
status&i=pxstat&i;
%end;
%mend;

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

Reeza — Mon, 15 Jan 2018 23:32:01 GMT

Its not 100% clear on what you're doing but I would probably drive the rules using a mapping table of some sort.

Can you illustrate your issue with some sample data?

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

shellp55 — Mon, 15 Jan 2018 23:40:12 GMT

Thanks for the speedy reply but the same error message is showing up "required operator not found in expression: &inst_no. in (&incl_list).

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

shellp55 — Mon, 15 Jan 2018 23:45:25 GMT

Reeza, I have many hospitals' data and it is in different formats and some fields are named differently. I actually have many of the same report per site which is labour intensive when I have to update so I would like one report where I enter the site in a variable and the report produces only for that site, I change the variable and run again.

In this instance, the field of CCI status is different for some sites versus others so I wanted to use the same rename macro as always BUT have an if statement as to what field to rename depending on the site. If it is sites a, b and c then the source field for the status&i = pxattr&i else the source is pxstat&i.

Thanks.

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

Reeza — Mon, 15 Jan 2018 23:47:41 GMT

I would say make a table with the variables for each report.

Then use a portion of your code to select the relevant names from that table. Otherwise you're hard coding the logic which is harder to maintain long term in my experience, especially if you're working with multiple people.

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

shellp55 — Mon, 15 Jan 2018 23:50:11 GMT

I am not sure what you are suggesting Reeza. The data comes from the main DAD data file per site. At the very least can you advise how to successfully use the in statement and save the variables i.e. with or without quotation marks? With or without commas? etc. Thanks.

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

Reeza — Mon, 15 Jan 2018 23:58:32 GMT

You don't need to do that. You can rename lists in a single statement.

rename status1-status20 = pxattr1-pxattr20;

So all you need to do, is define the prefix, if the 20 is fixed.

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

shellp55 — Tue, 16 Jan 2018 00:00:35 GMT

Interesting, thanks. I was building off of a previous response on this forum to rename fields for a single site which involved a macro. Glad to know it can be so much simpler, thanks.

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

Reeza — Tue, 16 Jan 2018 00:00:47 GMT

Something like the following would work - I'm not a fan of macro logic, in case you were wondering.

data _null_;

if &inst_no in (&incl_list) then prefix='pxattr';
else prefix='pxstat';

call symputx('prefix', prefix, 'g');

run;

data have;
set mydata;
rename status1-status20 = &prefix.1-&prefix.20;
run;

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

shellp55 — Tue, 16 Jan 2018 00:07:15 GMT

I'm trying to use the if statement with this and I am getting an error of "statement is not valid or it is used out of proper order.

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

Reeza — Tue, 16 Jan 2018 00:09:49 GMT

Post your code and log please.

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

shellp55 — Tue, 16 Jan 2018 00:17:24 GMT

Great, thanks Reeza!!

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

art297 — Tue, 16 Jan 2018 00:51:06 GMT

While you already have your answer, sometimes macros are the only or best way to accomplish some things. Not saying this is one of those times, but learning how to tame macros is IMHO an extremely important skill. That said, here is one way you could have gotten your macro to work:

data mydata;
  input status0-status20;
  cards;
0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0
;

%let inst_no=2222;
%let incl_list=3333,4444,5555,6666;

%macro renm1/mindelimiter=',';
  %if &inst_no. in &incl_list %then %do i = 0 %to 20;
    status&i=pxattr&i
  %end;

  %else %do i = 0 %to 20;
    status&i=pxstat&i
  %end;
%mend;

options minoperator;
data have;
  set mydata;
  rename %renm1;
run;

Art, CEO, AnalystFinder.com

Re: Macro using IF, THEN and ELSE, variable with in operator and do loop

shellp55 — Tue, 16 Jan 2018 00:53:11 GMT

Thank you so much! "Taming" is a great description and I will certainly tuck this code away for another day. Thanks for taking the time to respond.