turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Macro to automate derived fields. Please Help..Tha...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 10:18 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 11:23 AM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 10:27 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 11:01 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 11:14 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 11:23 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 11:54 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 12:04 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 12:07 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 12:11 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 12:11 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 12:14 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 05:56 PM

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

Thank You

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 07:44 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 08:52 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 06:33 PM

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.