topic Re: Merge/copy the values for the first observations across all observations in SAS Programming

Merge/copy the values for the first observations across all observations

guaguncher — Sat, 24 Aug 2019 15:42:31 GMT

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!

Re: Merge/copy the values for the first observations across all observations

novinosrin — Sat, 24 Aug 2019 15:59:16 GMT

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;

Re: Merge/copy the values for the first observations across all observations

Tom — Sat, 24 Aug 2019 15:53:54 GMT

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;