topic Re: Compare multiple columns in macro - arrays in macros? in SAS Programming

Compare multiple columns in macro - arrays in macros?

DangIT — Mon, 25 Aug 2014 19:36:03 GMT

Hi,

I have a dataset with a unique postal code and flags for ~80 campaigns that looks like:

FSALDU Cmp_201301 Cmp_201302 Cmp_201304 ....

A0A1B0 1 . 1

A0A1C0 . . 1

A0A1G0 1 . .

...

Data notes:

Campaign number is not consecutive
Flags on Campaign's are 1 or NULL

What I am trying to do is compare the flags on each postal code to create a 'Change' variable (Same, Added, Removed) comparing to the previous campaign.

For example my output dataset would look like:

FSALDU Cmp_chg_201302 Cmp_chg_201304 ...

A0A1B0 Removed Added

A0A1C0 Same Added

A0A1G0 Removed Same

...

To do this I create a macro with the comparison logic as:

%Macro Change(Prv_wk, Cur_wk) ;

IF Cmp_&Cur_wk=Cmp_&Prv_wk then Cmp_chg_&Cur_wk='Same' ;

IF Cmp_&Cur_wk=1 and Cmp_&Prv_wk=. then Cmp_chg_&Cur_wk='Added' ;

IF Cmp_&Cur_wk=. and Cmp_&Prv_wk=1 then Cmp_chg_&Cur_wk='Removed' ;

%mend ;

Within my Data Step I have:

Data Change_table ; Set Master_Data_tbl ;

%Change(201301,201302) ;

%Change(201302,201304) ;

Keep FSALDU Cmp_chg: ;

RUN ;

I could write the macro out 79 times to get the table i'm looking for, but I was wondering how I can optimize this code for automation. I imagine the solution would involve arrays and loops..

Thanks for your help!

Re: Compare multiple columns in macro - arrays in macros?

data_null__ — Mon, 25 Aug 2014 20:10:43 GMT

Something like this?

data have;
   input FSALDU $ Cmp_201301 Cmp_201302 Cmp_201304 cmp_201305;
   array cmp cmp_:;
   array chg[4] $1 chg_201301 chg_201302 chg_201304 chg_201305;
   do i = 2 to dim(cmp);
      if      cmp=cmp[i-1]         then chg='S'; 
      else if cmp=1 and cmp[i-1]=. then chg='A'; 
      else if cmp=. and cmp[i-1]=1 then chg='R'; 
      end; 
   drop; 
   cards; 
A0A1B0 1 . 1 1
A0A1C0 . . 1 .
A0A1G0 1 . . 1
;;;;
   run; 
proc print; 
   run;

Re: Compare multiple columns in macro - arrays in macros?

Astounding — Mon, 25 Aug 2014 20:18:32 GMT

Well, you don't actually describe what it means to optimize code for automation. Does it mean the fastest-running code, regardless of how complex it gets? Does it mean the simplest code that is still reasonably fast, so that it is easy to modify and maintain?

At any rate, data_null_ has a good approach. If you are confident about your incoming values always being 1 or missing, you can speed it up slightly:

      if cmp=cmp[i-1] then chg='S';
      else if cmp > cmp[i-1] then chg='A';
      else chg='R';

Good luck.

Re: Compare multiple columns in macro - arrays in macros?

DangIT — Mon, 25 Aug 2014 20:43:01 GMT

Astounding:

Good point. I mean optimized in terms of automation and not necessarily performance.

Data_Null:

Thanks for the reply.

I like the method, but every 2 weeks (sometimes 1) I will add a campaign, so the array for the Chg would have to be dynamic..

Re: Compare multiple columns in macro - arrays in macros?

Astounding — Mon, 25 Aug 2014 20:50:29 GMT

In that case, it would be wise to keep the code constant.

Instead of changing the code, maintain a separate file holding a list of the campaigns. The file could be a text file, a SAS data set, a table ... doesn't matter as long as your program can read it. Then the program should transform the list of campaigns into the variable names that the program will use.

What types of file(s) will your production system permit you to use, to hold and maintain a list of campaigns?

Some macro language will be involved but it will be minimal if you can store the list of campaigns as a SAS data set.

Re: Compare multiple columns in macro - arrays in macros?

DangIT — Mon, 25 Aug 2014 21:03:33 GMT

Yes, I can store store a separate SAS dataset with the list of campaigns.

How would I use that to create the arrays in the restructuring program?

Re: Compare multiple columns in macro - arrays in macros?

Astounding — Tue, 26 Aug 2014 02:44:30 GMT

OK. Let's assume you store a SAS data set with just one variable: WEEK

It should be character, with a length of 6, taking on values like 201301, 201302, etc. There will be one observation for each week.

Your program could look like this, to adjust automatically to the weeks in your data set:

proc sql noprint;

select distinct ('Cmp_' || week) into : campaign_list separated by ' ' from my.list_of_weeks;

select distinct ('Chg_' || week) into : change_list separated by ' ' from my.list_of_weeks;

quit;

%put _user_;

data want;

set have;

array camps {*} &campaign_list;

array changes {*} $ 7 &change_list;

do _i_=2 to dim(camps);

if cmp{_i_} = cmp{_i_-1} then changes{_i_}='Same';

else if cmp{_i_} > cmp{_i_-1} then changes{_i_}='Added';

else changes{_i_}='Removed';

end;

run;

The %put statement is only there to give you a feel for what you are getting with macro language. And you will have an extra "change" variable in your data set, since there is no way to assign the change that takes place in the first week. (It could be dropped, but that part would be more complex.) The program is easily maintained, since there is no maintenance. Just change the SAS data set holding the weeks and the program adjusts automatically.

Re: Compare multiple columns in macro - arrays in macros?

DangIT — Tue, 26 Aug 2014 14:04:53 GMT

Thank you for your help, this worked perfectly.