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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: