topic Re: Grouping variables together to perform calculations on the group in SAS Programming

Grouping variables together to perform calculations on the group

Rohit_R — Tue, 17 May 2016 10:28:19 GMT

Hi,

I have a dataset with variables denoting the statuses for the observations as at a month end. The key variables are 'StatusAtMonthend' and 'DaysAtStatusAtMonthend' which are repeated for each of the month ends going back several years. I want to calculate mean and median for the 'DaysAtStatusAtMonth' variable for each of the statuses in the 'StatusAtMonthend' variable.

I created two arrays in a datastep holding all the 'StatusAtMonthEnd' in one array and the 'DaysAtStatusAtMonthend' in another. However, I do not know how to calculate the mean/median in the data step, I think it requires a proc means, but it will be inefficient to write a proc means for each of the month ends for several years.

data stats;
set statuses;
array TCs {*} Status2015_01-Status2015_12;
array DaysTC {*} DaysAtStatus2015_01-DaysAtStatus_12;

run;

I want the mean/median days spent in each of the statuses at each of the month ends.

Please can someone point me in the right direction?

Thanks.

(Enterprise Guide 7.1)

Re: Grouping variables together to perform calculations on the group

Kurt_Bremser — Tue, 17 May 2016 11:02:27 GMT

Transpose the dataset from wide to long format with proc transpose, then you can use proc means.

Re: Grouping variables together to perform calculations on the group

Rohit_R — Tue, 17 May 2016 11:08:49 GMT

I played around a bit and thought of a way, however I am getting an error 'Too many arguments' for the execute subroutine.

%macro mean_med(dataset, status, DaysAtstatus);
proc means data=&dataset mean median;
class &status;
var &DaysAtStatus;
run;
%mend mean_med;

data stats1;
set stats;
array STs (*) ST2015_01-ST2015_12;
array DaysST (*) DaysAtStatus_2015_01-DaysAtTerritory_2015_12;
do i=1 to 12;
call execute("%mean_med(status1" || "," || STs(i)|| "," DaysST(i)|| ")");
end;
run;

Am I missing something in the call execute code?

Thanks.

Re: Grouping variables together to perform calculations on the group

Kurt_Bremser — Tue, 17 May 2016 11:20:20 GMT

When executing a macro with call execute, use single quotes for the macro call, or the macro will be expanded before the data step (that contains the call execute) runs.

Re: Grouping variables together to perform calculations on the group

Rohit_R — Tue, 17 May 2016 11:24:01 GMT

Hi Kurt, thank you for your quick replies. After using the single quotes, I am still getting following error message.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE,
GT, LE, LT, MAX, MIN, NE, NG, NL, OR, ^=, |, ||, ~=.

ERROR 253-185: The EXECUTE subroutine call has too many arguments.

Re: Grouping variables together to perform calculations on the group

Kurt_Bremser — Tue, 17 May 2016 11:26:15 GMT

You miss a concatenation operator between the second comma and DaysST(i).

Re: Grouping variables together to perform calculations on the group

Loko — Tue, 17 May 2016 11:27:23 GMT

Hello,

you have missing || before DaysST(i) in the call execute syntax.

Anyway i don't think you will get the desired results with this aproach...

Re: Grouping variables together to perform calculations on the group

Rohit_R — Tue, 17 May 2016 13:09:19 GMT

Thanks Loko, could you suggest an alternative?

Re: Grouping variables together to perform calculations on the group

ballardw — Tue, 17 May 2016 15:02:54 GMT

It would help to give a few example records and what the expected output should be.