I am struggling with this (seemingly) simple problem. I have a dataset where I obtained conditional means meanage1 and meanage2 from the proc means statements via outputting the values and merging.
However, I want to merge to be that the values for the first observation are copied throughout all ‘id’s in that column.
Here is what I have.
data have;
input id$ age meanage1 meanage2;
datalines;
1 41 33.2 28.3
2 32 . .
3 35 . .
4 29 . .
5 53 . .
6 41 . .
7 62 . .
8 72 . .
9 61 . .
;
run;
And this is dataset I want.
ID | Age | Meanage1 | Meanage2 |
1 | 41 | 33.2 | 28.3 |
2 | 32 | 33.2 | 28.3 |
3 | 35 | 33.2 | 28.3 |
4 | 29 | 33.2 | 28.3 |
5 | 53 | 33.2 | 28.3 |
6 | 41 | 33.2 | 28.3 |
7 | 62 | 33.2 | 28.3 |
8 | 72 | 33.2 | 28.3 |
9 | 61 | 33.2 | 28.3 |
Thanks!
hello @guaguncher It would help to rework your design upstream than to do this "fix" i.e if you show how you computed 2 means for one age var and you want it for all obs. Anyways the fix is simple
data have;
input id$ age meanage1 meanage2;
datalines;
1 41 33.2 28.3
2 32 . .
3 35 . .
4 29 . .
5 53 . .
6 41 . .
7 62 . .
8 72 . .
9 61 . .
;
run;
data want;
set have(drop=meanage:);
if _n_=1 then set have(keep=meanage: where=(meanage1>. and meanage2>.) obs=1);
run;
Alternatively, using Proc SQL:
proc sql;
create table want as
select id,age,max(meanage1) as meanage1,max(meanage2) as meanage2
from have;
quit;
Can you go back a step a prevent the generation of that goofy file to begin with?
data want ;
set have ;
if _n_=1 then set mean_age ;
run;
If not then one way is to make two NEW variables and retain them. Here is example for how to handle one. It is left as an exercise how to modify the data step to handle both.
data want ;
set have ;
if missing(meanage1) then meanage1=meanage1_new ;
else meanage1_new = meanage1;
retain meanage1_new ;
drop meanage1;
rename meanage1_new=meanage1;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.