Grouping variables together to perform calculations on the group

Reply
New Contributor
Posts: 4

Grouping variables together to perform calculations on the group

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)

Super User
Posts: 7,771

Re: Grouping variables together to perform calculations on the group

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Grouping variables together to perform calculations on the group

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.
Super User
Posts: 7,771

Re: Grouping variables together to perform calculations on the group

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Grouping variables together to perform calculations on the group

Posted in reply to KurtBremser
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.
Super Contributor
Posts: 308

Re: Grouping variables together to perform calculations on the group

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...

New Contributor
Posts: 4

Re: Grouping variables together to perform calculations on the group

Thanks Loko, could you suggest an alternative?
Super User
Posts: 7,771

Re: Grouping variables together to perform calculations on the group

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: Grouping variables together to perform calculations on the group

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

Ask a Question
Discussion stats
  • 8 replies
  • 483 views
  • 6 likes
  • 4 in conversation