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
Some ideas:
I didn't provide any code, because you have not provided data in usable form.
Some ideas:
I didn't provide any code, because you have not provided data in usable form.
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
If so, then please mark it as the solution 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.