topic Re: Dynamic SAS program to calculate mean per group for multiple numeric variables in SAS Programming

Dynamic SAS program to calculate mean per group for multiple numeric variables

Ronein — Tue, 07 Dec 2021 20:17:57 GMT

Hello

I want to creata a dynamic program that calculate for each category of Z the mean (average) of each Y variable.

The issue is that in this case have 3 Y variables ( Y202101 Y202103 Y202105) but in another time of running might have different Y variables .

I would like to create a program that is dynamic and adjust itself to situation when there are different Y variables

Data have;
input ID Z $ Y202101 Y202103  Y202105;
cards;
1	A	2	3	8
2	A	4	9	2
3	A	6	6	7
4	B	8	4	4
5	B	10	5	3
6	C	8	8	2
7	C	6	2	5
8	C	4	4	6
9	D	2	8	5
;
Run;

Re: Dynamic SAS program to calculate mean per group for multiple numeric variables

Reeza — Tue, 07 Dec 2021 20:20:16 GMT

So proc means with colon option on the VAR statement?

proc mean data=have MEAN ;
by ID;
var Y:;
output out=want1 mean= /autoname autolabel;
ods output summary = want2;
run;

Re: Dynamic SAS program to calculate mean per group for multiple numeric variables

PaigeMiller — Tue, 07 Dec 2021 21:56:00 GMT

Which is a fine solution as long as you don't have (dynamically) a character variable in the data set whose name begins with Y. And then you get an error.

The better solution, which we have recommended many times to @Ronein , and which I recommend to everyone, is to use a long data set instead of a wide data set. In the long data set, you have a variable named VALUE (or anything else you like) and a variable named MONTH, and then PROC SUMMARY/PROC MEANS will work with whatever values it finds in MONTH, dynamically, and go ahead and do the proper computations. And with the long data set, if you need a report where the months are columns, this is easy (and dynamic) via PROC REPORT.

I again urge you, @Ronein to embrace the long data set approach, rather than continually getting stuck trying to handle wide data sets.