topic Operations on unequally sized subsets of observations in SAS Data Management

Operations on unequally sized subsets of observations

MiniRadde — Fri, 01 Sep 2017 09:06:01 GMT

I have a problem that I hope a friendly soul out there could help me to solve.

I have a data set like

DAY	VALUE
1	1
1	2
1	3
1	6
2	8
2	6
2	4
3	15
3	10
3	35
3	20
3	5

and now I wish to do conditional operations so that for each DATE I create a new variable containing the average VALUE for that DAY, i.e.

DAY	VALUE	Mean_PER_DAY
1	1	3
1	2	3
1	3	3
1	6	3
2	8	6
2	6	6
2	4	6
3	15	17
3	10	17
3	35	17
3	20	17
3	5	17

This is just a small subset of my dataset that contains thousands of DATE-observations, so using if DATE=2 etc. is not a good solution.

Is there anyone who could write me some example code for this?

Thank you in advance!

Re: Operations on unequally sized subsets of observations

Astounding — Fri, 01 Sep 2017 10:56:57 GMT

You may need to debug my syntax here, but the simplest way would be to use SQL:

proc sql noprint;

create table want as

select *, mean(value) as mean_PER_DAY

from have

group by day;

quit;

Re: Operations on unequally sized subsets of observations

MiniRadde — Fri, 01 Sep 2017 11:12:50 GMT

Thank you so much @Astounding, I tried to do this by several rounds of datasteps and sorting. This really really helöped me a lot and saved me (probably several days) of extra work! Thank you!

Re: Operations on unequally sized subsets of observations

Rick_SAS — Wed, 06 Sep 2017 13:12:32 GMT

You can do this with PROC MEANS followed by a MERGE. the following assumes that the data are sorted by DAY:

proc means data=Have noprint;
by Day;
var Value;
output out=Means mean=Mean_Per_Day;
run;

data Want;
merge Have Means(keep=Day Mean_Per_Day);
by Day;
run;