Paneling Data by Month/Year

[Reeza]

@sklieger I edited your post to format code - not really required.

If I understand correctly this is common question. Do you have SAS/ETS?

Also, can you post data in the forum directly rather than as an Excel file, not everyone downloads files. 

[sklieger]

I think so?
271 PROC PRODUCT_STATUS;RUN;

For Base SAS Software ...
Custom version information: 9.4_M2
Image version information: 9.04.01M2P072314
For SAS/STAT ...
Custom version information: 13.2
For SAS/GRAPH ...
Custom version information: 9.4_M2
For SAS/ETS ...
Custom version information: 13.2

I tried to post data to the forum and it wasn't formatting correctly. I'll try again.

[sklieger]

HAVE (State, Start_Date, End_Date)

AK, 1/1/1980, 2/1/2016,

AR, 1/1/1988, 12/31/1990, 

WANT (State, Start_Date, End_Date, Mo_yearvar)

AK, 1/1/1980, 2/1/2016, 1/1980

AK, 1/1/1980, 2/1/2016, 2/1980

AK, 1/1/1980, 2/1/2016, 3/1980

AK, 1/1/1980, 2/1/2016, 4/1980

AK, 1/1/1980, 2/1/2016, 5/1980

...

AR, 1/1/1988, 12/31/1990, 1/1/1988

AR, 1/1/1988, 12/31/1990, 2/1/1988

AR, 1/1/1988, 12/31/1990, 3/1/1988

AR, 1/1/1988, 12/31/1990, 4/1/1988

...

[ballardw]

Something like this perhaps:

data date;
   set eitc;
   do Effect_Month = mdy(month(begin_date),1,year(begin_date) to mdy(month(end_date),1,year(end_date);
      output;
   end;
   format Effect_month   mmyys7.;
run;

adds a variable that is a date varaible with the value of the the first day of each month begin to end Formatted to look like month and year

[Ksharp]

data date;
   set eitc;
   do Effect_Month = mdy(month(begin_date),1,year(begin_date) )
                             to mdy(month(end_date),1,year(end_date));

if day(Effect_Month)=1 then   output;

   end;
   format Effect_month   mmyys7.;
run;

[ballardw]

right you are. Got distracted and missed the first day only output.

[sklieger]

Okay, definitely made some progress. Thanks for all the suggestions! Still having the issue of missing a month (i.e. no row of data) when begin_date is >14. What I want is for the previous month's data to carry forward so there are 12 months per year. I need some sort of code that says, if day >14, bump to next month AND carry forward previous month. Or something to that effect. Suggestions?

Here's my current code:

*Updated April 13, 2016;

libname eitc 'C:\Users\tub68397\Desktop\Projects\Legal Datasets\Panel Datasets for Nick';

*Import Excel Data;
PROC IMPORT OUT= work.eitc DATAFILE= "C:\Users\tub68397\Desktop\Projects\Legal Datasets\Panel Datasets for Nick\2_1_2016 EITC Data Stat.xlsx"
DBMS=xlsx REPLACE;
SHEET="2_1_2016 EITC Data";
GETNAMES=YES;
RUN;

*Extract Day, Month, Year & sort;
data sort;
set eitc;
day = day(begin_date);
month= month(begin_date);
year= year(begin_date);
run;

proc sort data=sort;
by day month year;
run;

*Panel Data by Month & Year, laws enacted on or after the 15th of the month, bump to subsequent month;
data panel;
set eitc;
if day(begin_date) < 15 then begin_date=intnx('month',begin_date,0);
else if day(begin_date) > 14 then begin_date=intnx('month',begin_date,1);
do i=1 to intck('month',begin_date,end_date);
mon_yr=cats(put(month(intnx('month',begin_date,i-1)),z2.),"-",put(year(intnx('month',begin_date,i-1)),z4.));
mon = 0;
mon = substr(mon_yr,1, 2);
yr = 0;
yr = substr(mon_yr,4, 4);
output;
end;
run;