topic Re: Max value by group, but keep all rows and columns in New SAS User

Max value by group, but keep all rows and columns

bkq32 — Sat, 26 Oct 2019 00:23:43 GMT

data have;
input tract_or_block tract $4. tract_per_white tract_per_black tract_per_hisp ses;
cards;
100 100 0.67 0.1 0.2 1.8
1001 100 . . . 1.5
1002 100 . . . 1.4
200 200 0.2 0.2 0.2 1.1
2001 200 . . . 1.2
2002 200 . . . 1.3
;
run;

I'm trying fill the missing values with the non-missing values by TRACT without getting rid of any rows or columns. I've tried the following code but can't figure out how to keep all the variables and records.

proc means data=have noprint nway;
 class TRACT
 var tract_per:;
 output out=want (drop= _TYPE_ _FREQ_) max= ;
run;

Re: Max value by group, but keep all rows and columns

novinosrin — Sat, 26 Oct 2019 00:37:43 GMT

Hi @bkq32 Your input data structure looks like a classic sample for LOCF logic as opposed to summary and merge.Am i right in my understanding?If yes,


data have;
input tract_or_block tract $4. tract_per_white tract_per_black tract_per_hisp ses;
cards;
100 100 0.67 0.1 0.2 1.8
1001 100 . . . 1.5
1002 100 . . . 1.4
200 200 0.2 0.2 0.2 1.1
2001 200 . . . 1.2
2002 200 . . . 1.3
;
run;


data want;
update have(obs=0) have;
by tract;
output;
run;

Re: Max value by group, but keep all rows and columns

bkq32 — Sat, 26 Oct 2019 00:42:14 GMT

Yes, this worked great. Thanks!