DATA Step, Macro, Functions and more

Macro to automate derived fields. Please Help..Thank You

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 95
Accepted Solution

Macro to automate derived fields. Please Help..Thank You

Hi All,

I would like to automate the calculation below , I have to create these calculated fields like 100 times as I have 100 variables to analyse, The example below only does it for 5 variables...Your help woul be really much appreciated!!

Many Thanks

V1_amt_32 = sum(V1_amt_mth_03, V1_amt_mth_02) / min(tenure, 2);
V1_amt_432 = sum(V1_amt_mth_04, V1_amt_mth_03, V1_amt_mth_02) / min(tenure, 3);

if V1_amt_mth_04 ne . or V1_amt_mth_05 ne . then V1_amt_54 = sum(V1_amt_mth_05, V1_amt_mth_04) / min(tenure, 2);

V2_amt_32 = sum(V2_amt_mth_03, V2_amt_mth_02) / min(tenure, 2);
V2_amt_432 = sum(V2_amt_mth_04, V2_amt_mth_03, V2_amt_mth_02) / min(tenure, 3);

if V2_amt_mth_04 ne . or V2_amt_mth_05 ne . then V2_amt_54 = sum(V2_amt_mth_05, V2_amt_mth_04) / min(tenure, 2);

V3_units_32 = sum(V3_units_mth_03, V3_units_mth_02) / min(tenure, 2);
V3_units_432 = sum(V3_units_mth_04, V3_units_mth_03, V3_units_mth_02) / min(tenure, 3);

if V3_units_mth_04 ne . or V3_units_mth_05 ne . then V3_units_54 = sum(V3_units_mth_05, V3_units_mth_04) / min(tenure, 2);

V4_32 = sum(V4_mth_03, V4_mth_02) / min(tenure, 2);
V4_432 = sum(V4_mth_04, V4_mth_03, V4_mth_02) / min(tenure, 3);

if V4_mth_04 ne . or V4_mth_05 ne . then V4_54 = sum(V4_mth_05, V4_mth_04) / min(tenure, 2);

V5_32 = sum(V5_mth_03, V5_mth_02) / min(tenure, 2);
V5_432 = sum(V5_mth_04, V5_mth_03, V5_mth_02) / min(tenure, 3);
if V5_mth_04 ne . or V5_mth_05 ne . then V5_54 = sum(V5_mth_05, V5_mth_04) / min(tenure, 2);



Accepted Solutions
Solution
‎12-03-2014 11:23 AM
Super User
Posts: 5,498

Re: Macro to automate derived fields. Please Help..Thank You

RW9's suggestion about arrays is a good one.  If you are looking for an introduction to using macros, this would be a good case to get started.  Define a macro that contains the calculations for one variable:

%macro calculate (varname);

&varname._32 = sum(&varname._mth_03, &varname._mth_02) / min(tenure, 2);

&varname._432 = sum(&varname._mth_04, &varname._mth_03, &varname._mth_02) / min(tenure, 3);

if &varname._mth_04 ne . or &varname._mth_05 ne . then

&varname._54 = sum(&varname._mth_05, &varname._mth_04) / min(tenure, 2);

%mend calculate;

Then begin the learning process by calling the macro as many times as needed:

data want;

set have;

%calculate (Sales)

%calculate (Disc)

%calculate (Units)

run;

Once it is clear how that works, look for a way to further automate.  That would involve writing another macro that uses a list of prefixes to generate the equivalent macro calls, such as:

data want;

set have;

%expand (Sales Disc Units)

run;

But take it one step at a time, until you are comfortable with writing the code that gets you through that step.

Good luck.

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: Macro to automate derived fields. Please Help..Thank You

Hi,

data _null_;

     call execute('data want; set have;');

     do I=1 to 100;

     call execute('V'||strip(put(I,best.))||'_amt_32 = sum(V'||strip(put(I,best.))||'_amt_mth_03, V'||strip(put(I,best.))||'_amt_mth_02) / min(tenure, 2);

                          V'||strip(put(I,best.))||'_amt_432 = sum(V'||strip(put(I,best.))||'_amt_mth_04, V'||strip(put(I,best.))||'_amt_mth_03, V'||strip(put(I,best.))||'_amt_mth_02) / min(tenure, 3);

                         if V'||strip(put(I,best.))||'_amt_mth_04 ne . or V'||strip(put(I,best.))||'_amt_mth_05 ne . then V'||strip(put(I,best.))||'_amt_54 = sum(V'||strip(put(I,best.))||'_amt_mth_05, V'||strip(put(I,best.))||'_amt_mth_04) / min(tenure, 2);');

     end;

     call execute('run;');

run;

This will generate your 100 statement datastep replacing I each time.  However I would question why you are going in this route at all.  Put categories into your data, have a row per category, and then do you calculation, far easier.  Alternative learn about arrays, they are built to work on many variables.

Frequent Contributor
Posts: 95

Re: Macro to automate derived fields. Please Help..Thank You

Thank you! Sorry I forgot to mention that te varaibles have different names so V1 is Sales, V2 is Units..etc...so basically the variables are like below

Sales_amt_32 = sum(Sales_amt_mth_03, Sales_amt_mth_02) / min(tenure, 2);
Sales_amt_432 = sum(Sales_amt_mth_04, Sales_amt_mth_03, Sales_amt_mth_02) / min(tenure, 3);
if Sales_amt_mth_04 ne . or Sales_amt_mth_05 ne . then Sales_amt_54 = sum(Sales_amt_mth_05, Sales_amt_mth_04) / min(tenure, 2);
Disc_amt_32 = sum(Disc_amt_mth_03, Disc_amt_mth_02) / min(tenure, 2);
Disc_amt_432 = sum(Disc_amt_mth_04, Disc_amt_mth_03, Disc_amt_mth_02) / min(tenure, 3);
if Disc_amt_mth_04 ne . or Disc_amt_mth_05 ne . then Disc_amt_54 = sum(Disc_amt_mth_05, Disc_amt_mth_04) / min(tenure, 2);
Units_32 = sum(Units_mth_03, Units_mth_02) / min(tenure, 2);
Units_432 = sum(Units_mth_04, Units_mth_03, Units_mth_02) / min(tenure, 3);
if Units_mth_04 ne . or Units_mth_05 ne . then Units_54 = sum(Units_mth_05, Units_mth_04) / min(tenure, 2);
Trips_32 = sum(Trips_mth_03, Trips_mth_02) / min(tenure, 2);
Trips_432 = sum(Trips_mth_04, Trips_mth_03, Trips_mth_02) / min(tenure, 3);
if Trips_mth_04 ne . or Trips_mth_05 ne . then Trips_54 = sum(Trips_mth_05, Trips_mth_04) / min(tenure, 2);
days_32 = sum(days_mth_03, days_mth_02) / min(tenure, 2);
days_432 = sum(days_mth_04, days_mth_03, days_mth_02) / min(tenure, 3);
if days_mth_04 ne . or days_mth_05 ne . then days_54 = sum(days_mth_05, days_mth_04) / min(tenure, 2);
Super User
Super User
Posts: 7,942

Re: Macro to automate derived fields. Please Help..Thank You

Am not re-writing it again.  If the variables are not the same then you would need to put that information in somehow.  You could do that by array for example, or a dataset.  There's no coding way of know that 1 = Sales.

data _null_;

     array vals{100} $200. ("sales","...);

     call execute('data want; set have;');

     do I=1 to 100;

     call execute('V'||strip(vals{I})||'_amt_32 = sum...

Again though, think about your data differently, you will have further troubles down the line when you try to do anything with this data.  Try to get to a stage where your dataset looks like:

section     amt_mth_03     amt_mth_04 ...

sales          xyz                    xyz

rather than separate variables for each

Solution
‎12-03-2014 11:23 AM
Super User
Posts: 5,498

Re: Macro to automate derived fields. Please Help..Thank You

RW9's suggestion about arrays is a good one.  If you are looking for an introduction to using macros, this would be a good case to get started.  Define a macro that contains the calculations for one variable:

%macro calculate (varname);

&varname._32 = sum(&varname._mth_03, &varname._mth_02) / min(tenure, 2);

&varname._432 = sum(&varname._mth_04, &varname._mth_03, &varname._mth_02) / min(tenure, 3);

if &varname._mth_04 ne . or &varname._mth_05 ne . then

&varname._54 = sum(&varname._mth_05, &varname._mth_04) / min(tenure, 2);

%mend calculate;

Then begin the learning process by calling the macro as many times as needed:

data want;

set have;

%calculate (Sales)

%calculate (Disc)

%calculate (Units)

run;

Once it is clear how that works, look for a way to further automate.  That would involve writing another macro that uses a list of prefixes to generate the equivalent macro calls, such as:

data want;

set have;

%expand (Sales Disc Units)

run;

But take it one step at a time, until you are comfortable with writing the code that gets you through that step.

Good luck.

Frequent Contributor
Posts: 95

Re: Macro to automate derived fields. Please Help..Thank You

Posted in reply to Astounding

Thank you, I get the error below when I run the code..Could you please let me know the problem? Cheers

14         GOPTIONS ACCESSIBLE;
SYMBOLGEN:  Macro variable VARNAME resolves to and_mktg_voucher_amt
15         %calculate (and_mktg_voucher_amt);
SYMBOLGEN:  Macro variable VARNAME resolves to and_mktg_voucher_amt
NOTE: Line generated by the macro variable "VARNAME".
15          and_mktg_voucher_amt_32
            _______________________
ERROR 180-322: Statement is not valid or it is used out of proper order.

Super User
Posts: 5,498

Re: Macro to automate derived fields. Please Help..Thank You

Diagnosis requires a little more information to work with.  Please post your macro definition (just to verify that it was properly duplicated) as well as the DATA step that calls the macro.  There's probably an easy answer there.

Frequent Contributor
Posts: 95

Re: Macro to automate derived fields. Please Help..Thank You

Sorry , I have fixed the error :-) Thanks a lot for your help...What does the %expand macro do?

Super User
Posts: 5,498

Re: Macro to automate derived fields. Please Help..Thank You

The concept of an %expand macro would save a little typing.  You would enter:

%expand (Sales Disc Units)

It would convert this to:

%calculate (Sales)

%calculate (Disc)

%Calculate (Units)

But it's not an existing macro ... and takes a little more skill to write than %calculate.

Frequent Contributor
Posts: 95

Re: Macro to automate derived fields. Please Help..Thank You

Another question, is there any other way to write this macro, withouth showing all the name of variables...I wanted to keep non manual as much as I can...

Thank you

Super User
Posts: 5,498

Re: Macro to automate derived fields. Please Help..Thank You

Yes, but you would have to have a source that would supply all the names.  How would you get the names of the variables?

Frequent Contributor
Posts: 95

Re: Macro to automate derived fields. Please Help..Thank You

Posted in reply to Astounding

I can get it from a proc contents of a table that I am using! Does it answer your question?

Thank You

Super User
Posts: 5,498

Re: Macro to automate derived fields. Please Help..Thank You

OK, taking that approach here are some things you can do.  First, get the list of variable names into a macro variable:

proc contents data=have noprint out=_contents_ (keep=name type where=(type=1));

run;

proc sql noprint;

select strip(name) into : list_of_vars separated by ' ' from _contents_;

quit;

%put &list_of_vars;

There are other ways to do this, and you may have to tweak the list a bit before running the final SQL.  For example, this list contains all the numeric variables and you may have to shorten that to get just the prefixes

Then write a macro that can process each name in the list:

%macro expand (var_list=);

  %local i next_name;

  %do i=1 %to %sysfunc(countw(&var_list));

     %let next_name = %scan(&var_list, &i);

     %calculate (&next_name)

  %end;

%mend expand;

Then use the macros in a DATA step:

data want;

  set have;

  %expand (var_list=&list_of_names)

run;

That's the basic outline ... you can tweak it as needed and learn the pieces as you go.

Good luck.

Frequent Contributor
Posts: 95

Re: Macro to automate derived fields. Please Help..Thank You

Posted in reply to Astounding

Thank you very much Astounding! Your answer is Outstanding :-)

I will try this and will let you know how it goes...

Respected Advisor
Posts: 3,799

Re: Macro to automate derived fields. Please Help..Thank You

Seems like you have a lot of DATA coded into the meta data (variable names).  If you normalized your data the problem will be much simpler.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 483 views
  • 6 likes
  • 4 in conversation