SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Operations on unequally sized subsets of observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Operations on unequally sized subsets of observations

[ Edited ]

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!


Accepted Solutions
Solution
‎09-01-2017 07:11 AM
Super User
Posts: 6,632

Re: Operations on unequally sized subsets of observations

Posted in reply to MiniRadde

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;

View solution in original post


All Replies
Solution
‎09-01-2017 07:11 AM
Super User
Posts: 6,632

Re: Operations on unequally sized subsets of observations

Posted in reply to MiniRadde

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;

Occasional Contributor
Posts: 11

Re: Operations on unequally sized subsets of observations

Posted in reply to Astounding

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!

SAS Super FREQ
Posts: 4,171

Re: Operations on unequally sized subsets of observations

Posted in reply to MiniRadde

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 464 views
  • 0 likes
  • 3 in conversation