Greetings, I have a data structure that looks like this: DATA have ; INPUT famid indid implicate imp_inc; CARDS ; 1 1 1 40000 1 1 2 25000 1 1 3 34000 1 1 4 23555 1 1 5 49850 1 2 1 1000 1 2 2 2000 1 2 3 3000 1 2 4 4000 1 2 5 5000 1 3 1 . 1 3 2 . 1 3 3 . 1 3 4 . 1 3 5 . 2 1 1 40000 2 1 2 45000 2 1 3 50000 2 1 4 34000 2 1 5 23500 2 2 1 . 2 2 2 . 2 2 3 . 2 2 4 . 2 2 5 . 2 3 1 41000 2 3 2 39000 2 3 3 24000 2 3 4 32000 2 3 5 53000 RUN ; So, we have family id, individual id, implicate number and imputed income for each implicate. What i need is to replicate the results of the first individual in each family (all of the five implicates) for the remaining individuals within each family, replacing whatever values we previously had on those cells, like this: DATA want ;
INPUT famid indid implicate imp_inc;
CARDS ;
1 1 1 40000
1 1 2 25000
1 1 3 34000
1 1 4 23555
1 1 5 49850
1 2 1 40000
1 2 2 25000
1 2 3 34000
1 2 4 23555
1 2 5 49850
1 3 1 40000
1 3 2 25000
1 3 3 34000
1 3 4 23555
1 3 5 49850
2 1 1 40000
2 1 2 45000
2 1 3 50000
2 1 4 34000
2 1 5 23500
2 2 1 40000
2 2 2 45000
2 2 3 50000
2 2 4 34000
2 2 5 23500
2 3 1 40000
2 3 2 45000
2 3 3 50000
2 3 4 34000
2 3 5 23500
RUN ; In this example I'm trying to replicate only one variable but in my project I will have to do this for dozens of variables. So far, I came up with this solution: %let implist_1=imp_inc;
%macro copyv1(list);
%let nwords=%sysfunc(countw(&list));
%do i=1 %to &nwords;
%let varl=%scan(&list, &i);
proc means data=have max noprint;
var &varl;
by famid implicate;
where indid=1;
OUTPUT OUT=copy max=max_&varl;
run;
data want;
set have;
drop &varl;
run;
data want (drop=_TYPE_ _FREQ_);
merge want copy;
by famid implicate;
rename max_&varl=&varl;
run;
%end;
%mend;
%copyv1(&imp_list1); This works well for one or two variables. However it is tremendously slow once you do it for 400 variables in a data-set with the size of 1.5 GB. I'm pretty sure there is a faster way to do this with some form of proc sql or first.var etc., but i'm relatively new to SAS and so far I couldn't come up with a better solution. Thank you very much for your support. Best regards
... View more
