topic Re: fill in missing values with the previous values for panel in SAS Programming

fill in missing values with the previous values for panel

Jarvin99 — Fri, 15 Dec 2023 15:56:21 GMT

data have;  
   input gvkey $ year shrcd;  
   datalines;  
2477 2001 11
2477 2002 11
2477 2003 
3156 2001 10
3156 2002 10
3156 2003 
3156 2004 
3010 2001 
3010 2002 
3010 2003 
3010 2004 
;  
proc print data=have;  
run;

Hi everyone,

I am new to SAS, so the data step to input the data that I am writing here may contain some flaws, but I did my best to help you visualize my panel. I only want to fill in the column "shrcd" (as there actually many columns in my dataset with missing values) if this gvkey once had its shrcd filled in.

Hence, gvkey 2477 should have its 2003's shrcd as 11, and gvkey 3156 should have both its 2003 and 2004 filled in as 10, but gvkey 3010 should NOT have anything filled in for its shrcd.

Could I kindly seek assistance on how to do my request? Thank you.

Re: fill in missing values with the previous values for panel

sbxkoenk — Fri, 15 Dec 2023 16:24:08 GMT

If you are working with Time Series Data and Panel data, you will have SAS/ETS and SAS Econometrics available.
So you can use PROC EXPAND for this task.

data have;  
   input gvkey $ year shrcd;
   yeardt=MDY(12,31,year);
   format yeardt date9.; 
   datalines;  
2477 2001 11
2477 2002 11
2477 2003 . 
3156 2001 10
3156 2002 10
3156 2003 .
3156 2004 .
3010 2001 .
3010 2002 .
3010 2003 .
3010 2004 .
;  
*proc print data=have; *run;
*proc sort data=have; *by gvkey yeardt; *run;

proc expand data=have out=want 
            method=step EXTRAPOLATE;
 by gvkey notsorted;
 id yeardt;
 var shrcd;
run;
proc print data=want; run;
/* end of program */

Koen

Re: fill in missing values with the previous values for panel

Tom — Fri, 15 Dec 2023 16:35:38 GMT

You can use the UPDATE statement to implement last observation carried forward. To prevent it from applying to the other variables just re-read the observation without the ones you do want carried forward.

data want;
  update have(obs=0) have;
  by gvkey;
  set have(drop=shrcd);
  output;
run;

If you want to move values backwards in time then you will need a different method.