topic Re: Filling in Entries for entire time period in SAS Programming

Filling in Entries for entire time period

Sandi1 — Tue, 02 Mar 2021 07:39:51 GMT

Hi dear experts,

I need to fill entries for entire time period using earlier symbol. For example, I have a company (GVKEY 100) that starts from Dec 2010 and ends at May 2011. Symbol for first date (Dec 2010) is A, then the next one is B which is on April 2011. what i need is to create consecutive; "year" "month" for the entire time period (Dec 2010 to May 2011) and plug in the earlier Symbol for the missing.

Please note; I have many of the distinct companies (see below for two GVKEY, 100 and 120).

Data Have;

GVKEY	Year	Month	Symbol
100	2010	12	A
100	2011	4	B
100	2011	5	BB
120	2013	11	D
120	2014	2	C
120	2014	4	B

Data Want;

GVKEY	Year	Month	Symbol
100	2010	12	A
100	2011	1	A
100	2011	2	A
100	2011	3	A
100	2011	4	B
100	2011	5	BB
120	2013	11	D
120	2013	12	D
120	2014	1	D
120	2014	2	C
120	2014	3	C
120	2014	4	B

thanks

Re: Filling in Entries for entire time period

andreas_lds — Tue, 02 Mar 2021 08:03:20 GMT

Some ideas:

use "month" and "year" to create a real sas-date, named "date"
process the data "by gvkey"
calculate the next expected "date" using intnx and store it in a retained variable
if date does not match the retained date use a loop to create the missing obs

I didn't provide any code, because you have not provided data in usable form.

Re: Filling in Entries for entire time period

PeterClemmensen — Tue, 02 Mar 2021 08:25:05 GMT

Try this


data have;
input GVKEY $ Year Month Symbol $;
datalines;
100 2010 12 A  
100 2011 4  B  
100 2011 5  BB 
120 2013 11 D  
120 2014 2  C  
120 2014 4  B  
;

data want(keep = GVKEY Year Month Symbol);
   merge have have(firstobs = 2 rename = (GVKEY = g Year = y Month = m) drop = Symbol);

   dt1 = mdy(Month, 1, Year);
   dt2 = mdy(m, 1, y);
   
   i = intck('month', dt1, dt2);

   if GVKEY = g & i > 1 then do j = 0 to i - 1;
      dt = intnx('month', dt1, j);
      Month = month(dt);
      Year = year(dt);
      output;
   end;

   else output;
run;

Result:

GVKEY  Year  Month  Symbol 
100    2010  12     A 
100    2011  1      A 
100    2011  2      A 
100    2011  3      A 
100    2011  4      B 
100    2011  5      BB 
120    2013  11     D 
120    2013  12     D 
120    2014  1      D 
120    2014  2      C 
120    2014  3      C 
120    2014  4      B

Re: Filling in Entries for entire time period

Sandi1 — Wed, 03 Mar 2021 22:56:01 GMT

Thank you PeterClemmensen
JADE

Re: Filling in Entries for entire time period

Sandi1 — Wed, 03 Mar 2021 22:57:45 GMT

This is the solution

Re: Filling in Entries for entire time period

PeterClemmensen — Thu, 04 Mar 2021 06:30:43 GMT

If so, then please mark it as the solution 🙂