topic Re: Populate data values using values of other rows in SAS Programming

Populate data values using values of other rows

lichee — Wed, 10 May 2023 19:18:02 GMT

I'm trying to replace missing values with the values of the previous row before the row with missing. The example data is as below: the data set "have" is the data I have, and "want" is the target data I want to generate. The idea is that when the fields ID and var1 have missing values, the row with missing takes the value from the row with value before the row with missing value.

Thank you very much!

data have;
infile datalines truncover dsd;
input Person_ID var1 $ var2 $;
datalines;
1,aa,aa1
,,aa2
2,bb,bb
3,cc,cc
4,dd,dd
5,ee,ee
6,ff,ff
7,gg,gg1
,,gg2
,,gg3
8,hh,hh1
,,hh2
,,hh3
;
run;

data want;
infile datalines truncover dsd;
input Person_ID var1 $ var2 $;
datalines;
1,aa,aa1
1,aa,aa2
2,bb,bb
3,cc,cc
4,dd,dd
5,ee,ee
6,ff,ff
7,gg,gg1
7,gg,gg2
7,gg,gg3
8,hh,hh1
8,hh,hh2
8,hh,hh3
;
run;

Re: Populate data values using values of other rows

data_null__ — Wed, 10 May 2023 19:27:21 GMT

Try the update trick.

data have;
	infile datalines truncover dsd;
	input Person_ID var1 $ var2 $;
	datalines;
1,aa,aa1
,,aa2
2,bb,bb
3,cc,cc
4,dd,dd
5,ee,ee
6,ff,ff
7,gg,gg1
,,gg2
,,gg3
8,hh,hh1
,,hh2
,,hh3
;
run;

data have2;
   retain dummy  1;
   set have;
   run;

data want;
   update have2(obs=0) have2;
   by dummy;
   output;
   drop dummy;
   run;
proc print; run;

Re: Populate data values using values of other rows

lichee — Wed, 10 May 2023 20:50:35 GMT

Thanks a lot!

The code works for the dummy data. What if there are many other fields with missing values but other fields do not need to be populated for their missing values in the same way? For example, if I only wanted to populate var1 but not var2, can we specify fields to be populated? Thanks again!

Re: Populate data values using values of other rows

data_null__ — Wed, 10 May 2023 21:57:43 GMT

Use the UPDATE/BY statements and keep (data set options) only the variables that you want to carry forward. Then use SET dropping the variables you kept in the UPDATE statement.

If you supply some sample data I will make example.

if 0 then set have2;
update have2(keep=dummy obs=0) have2(keep=dummy x y z);
by dummy;
set have2(drop=dummy x y z);

Re: Populate data values using values of other rows

MayurJadhav — Wed, 10 May 2023 22:24:27 GMT

Here is the solution to retain values on multiple variables and assign the values on the basis of different conditions. The variables may have different or the same conditions.

In this example I've used following conditions: Person_ID eq . var1 eq'' var2 eq ''

But it can be easily changed and adjusted according to your requirement. @data_null__

data have;
	infile datalines truncover dsd;
	input Person_ID var1 $ var2 $;
	datalines;
1,aa,aa1
,,aa2
2,bb,bb
3,cc,cc
4,dd,dd
5,ee,ee
6,ff,ff
7,gg,gg1
,,gg2
,,gg3
8,hh,hh1
,,hh2
,,hh3
;
run;
proc print;

data want;
    set have;
    retain temp1 temp2 temp3;
    if _N_=1 then do;
    	temp1=Person_ID; 
    	temp2=var1; 
    	temp3=var2; 
    	output; 
    	end;
    else do; 
    	/* for Person_ID */
    	if Person_ID eq . then do; 
    		temp1=temp1; 
    		Person_ID=temp1; 
    		end;
    	else do; 
    		temp1=Person_ID; 
    		Person_ID=Person_ID; 
    		end;
     	
     	/* for var1 */
    	if var1 eq '' then do; 
    		temp2=temp2; 
    		var1=temp2; 
    		end;
    	else do; 
    		temp2=var1; 
    		var1=var1; 
    		end;
     	
     	/* for var2 */
    	if var2 eq '' then do; 
    		temp3=temp3; 
    		var2=temp3; 
    		end;
    	else do; 
    		temp3=var2; 
    		var2=var2; 
    		end;
     	
     output;
     temp1=Person_ID;
     temp2=var1;
     temp3=var2;
     
     end;
   drop temp1 temp2 temp3;
 run;
 proc print;

Re: Populate data values using values of other rows

lichee — Thu, 11 May 2023 02:33:08 GMT

The dummy data would look like below. Basically only Person_ID and var1 need to be populated (as from data have to data want), but not var3 or var4 even thought they have missing values as well.

data have;
infile datalines truncover dsd;
input Person_ID var1 $ var2 $ var3 $ var4;
datalines;
ID,var1,var2,var3,var4
1,aa,aa1,aaa,
,,aa2,,10
2,bb,bb,bbb,6
3,cc,cc,ccc,8
4,dd,dd,ddd,
5,ee,ee,eee,7
6,ff,ff,fff,
7,gg,gg1,ggg,3
,,gg2,,5
,,gg3,,6
8,hh,hh1,hhh,
,,hh2,,
,,hh3,,
;
run;

data want;
infile datalines truncover dsd;
input Person_ID var1 $ var2 $ var3 $ var4;
datalines;
1,aa,aa1,aaa,
1,aa,aa2,,10
2,bb,bb,bbb,6
3,cc,cc,ccc,8
4,dd,dd,ddd,
5,ee,ee,eee,7
6,ff,ff,fff,
7,gg,gg1,ggg,3
7,gg,gg2,,5
7,gg,gg3,,6
8,hh,hh1,hhh,
8,hh,hh2,,
8,hh,hh3,,
;
run;

Re: Populate data values using values of other rows

lichee — Thu, 11 May 2023 02:35:05 GMT

Sorry! Data have should be as below:

data have;
infile datalines truncover dsd;
input Person_ID var1 $ var2 $ var3 $ var4;
datalines;
1,aa,aa1,aaa,
,,aa2,,10
2,bb,bb,bbb,6
3,cc,cc,ccc,8
4,dd,dd,ddd,
5,ee,ee,eee,7
6,ff,ff,fff,
7,gg,gg1,ggg,3
,,gg2,,5
,,gg3,,6
8,hh,hh1,hhh,
,,hh2,,
,,hh3,,
;
run;

Thank you!

Re: Populate data values using values of other rows

MayurJadhav — Thu, 11 May 2023 10:59:53 GMT

This method should work! Looking at your input dataset you just need to add var4 with condition:

if var4 eq . then do

It's a bit long code but simple and easy to understand. It would be possible to further optimize this code, possibly using ARRAY or other sas procedures.

But this method is good enough to achieve desired output.

@lichee

Re: Populate data values using values of other rows

data_null__ — Thu, 11 May 2023 13:09:29 GMT

Here you go.

data have;
infile datalines truncover dsd;
input Person_ID var1 $ var2 $ var3 $ var4;
datalines;
1,aa,aa1,aaa,
,,aa2,,10
2,bb,bb,bbb,6
3,cc,cc,ccc,8
4,dd,dd,ddd,
5,ee,ee,eee,7
6,ff,ff,fff,
7,gg,gg1,ggg,3
,,gg2,,5
,,gg3,,6
8,hh,hh1,hhh,
,,hh2,,
,,hh3,,
;
run;

data have2;
   retain dummy 1;
   set have;
   run;
proc print; run;
data want;
   if 0 then set have2;
   update have2(keep=dummy obs=0) have2(keep=dummy Person_id var1-var2);
   by dummy;
   set have2(keep=var3-var4);
   output;
   drop dummy;
   run;
proc print;
   run;