@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.
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
...
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
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.