topic Re: Macro to automate derived fields. Please Help..Thank You in SAS Programming

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

Kanyange — Wed, 03 Dec 2014 15:18:36 GMT

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);

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

RW9 — Wed, 03 Dec 2014 15:27:23 GMT

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.

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

Kanyange — Wed, 03 Dec 2014 16:01:48 GMT

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);

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

RW9 — Wed, 03 Dec 2014 16:14:55 GMT

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

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

Astounding — Wed, 03 Dec 2014 16:23:38 GMT

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.

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

Kanyange — Wed, 03 Dec 2014 16:54:27 GMT

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.

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

Astounding — Wed, 03 Dec 2014 17:04:09 GMT

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.

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

Kanyange — Wed, 03 Dec 2014 17:07:30 GMT

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

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

Astounding — Wed, 03 Dec 2014 17:11:22 GMT

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.

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

Kanyange — Wed, 03 Dec 2014 17:11:41 GMT

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

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

Astounding — Wed, 03 Dec 2014 17:14:45 GMT

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

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

Kanyange — Wed, 03 Dec 2014 22:56:55 GMT

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

Thank You

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

data_null__ — Wed, 03 Dec 2014 23:33:34 GMT

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.

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

Astounding — Thu, 04 Dec 2014 00:44:16 GMT

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.

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

Kanyange — Thu, 04 Dec 2014 01:52:21 GMT

Thank you very much Astounding! Your answer is Outstanding 🙂

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