topic Re: Add individual fields summarized by a macro variable in SAS Programming

Add individual fields summarized by a macro variable

texasmfp — Tue, 19 Jan 2021 00:08:52 GMT

Hi:

I am trying to automate a task. I create a macro variable as follows:

%Let weightvar = MATERIALS LABOR VOH FOH GNA INTEREST;

MATERIALS LABOR VOH FOH GNA INTEREST are field names in a database.

I weight average these in a PROC MEANS step.

Later, in a data step, I want to add these variables together to form a new field TOTALCOST.

However, I want to run the prog multiple times, each time adding or subtracting one or more of the fields in the weightvar line.

Obviously, TOTALCOST=sum(&weightvar); does not work. What are some options to effect this? Thanks

Re: Add individual fields summarized by a macro variable

qoit — Tue, 19 Jan 2021 00:35:32 GMT

Not sure if I understand, but are you able to share the code? If the variables will remain constant, you can always you:

TotalCost = SUM(MATERIALS,LABOR,VOH,FOH,GNA,INTEREST) *Separated by commas;

If you want to execute them conditionally, such as adding or subtracting the variables, wrap it in a macro and use %IF statements to generate SAS code.

Re: Add individual fields summarized by a macro variable

Reeza — Tue, 19 Jan 2021 00:40:20 GMT

Use the TRANSLATE function to add commas.

You could either create a second macro variable at the top of your program or use it inline in your code as desired.

%macro addCommas(varList=);
%sysfunc(translate(&varList, %str(, ), %str( )))
%mend addCommas;

%let comma_delimited_list = %addCommas(varList = MATERIALS LABOR VOH FOH GNA INTEREST);

%put &comma_delimited_list;

You could use it directly inline with your code if you wanted, something like:

sumVariables = sum(%addCommas(varList = &weightVar.));

Re: Add individual fields summarized by a macro variable

Reeza — Tue, 19 Jan 2021 00:43:27 GMT

And if you were doing this for various different variable combinations I would assume you were using some of the combinatorial functions in SAS along with CALL EXECUTE(). You could just create two macro variables within that step one that was comma delimited and one that was space delimited to pass to the macro.

Re: Add individual fields summarized by a macro variable

texasmfp — Tue, 19 Jan 2021 00:55:33 GMT

Yes, to test lots of combinations, I intend to automate using a series of call execute

Re: Add individual fields summarized by a macro variable

texasmfp — Tue, 19 Jan 2021 00:58:14 GMT

that's just it, the variables are not constant, the list is ever changing (or rather is a series of all possible combinations). For example:

%Let weightvar = MATERIALS LABOR VOH FOH GNA INTEREST;
%Let weightvar = LABOR VOH FOH GNA INTEREST;
%Let weightvar = VOH FOH GNA INTEREST;
%Let weightvar = MATERIALS VOH FOH GNA INTEREST;
%Let weightvar = MATERIALS VOH GNA INTEREST;

etc....