BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kanyange
Fluorite | Level 6

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


1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

14 REPLIES 14
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kanyange
Fluorite | Level 6

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);
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Astounding
PROC Star

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.

Kanyange
Fluorite | Level 6

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.

Astounding
PROC Star

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.

Kanyange
Fluorite | Level 6

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

Astounding
PROC Star

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.

Kanyange
Fluorite | Level 6

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

Astounding
PROC Star

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

Kanyange
Fluorite | Level 6

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

Thank You

Astounding
PROC Star

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.

Kanyange
Fluorite | Level 6

Thank you very much Astounding! Your answer is Outstanding 🙂

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

data_null__
Jade | Level 19

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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