Hi, I need help expanding my data which have observations hiredate and termdate between 2001 and 2005. I need to create two columns: one for months and one for year. So for instance, the first observation would expand all months and year from jan 2001 to dec 2008. I have tried a DO OUTPUT loop but having some trouble. Any help is appreciated.
EMPID | FIRST | RECTYPE | STORE | RATE | HIREDATE | TERMDATE |
Z126330 | LARRY | HIRE | 1 | 10.97 | 3/21/2000 | 1/31/2015 |
Z128570 | BILL | TERM | 1 | 8.66 | 10/2/2000 | 3/7/2002 |
Z187169 | LISA | TERM | 1 | 9.98 | 4/22/2005 | 11/1/2006 |
Z270823 | TERESA | HIRE | 1 | 8 | 10/2/2000 | 1/31/2015 |
Z284042 | SAM | HIRE | 1 | 9.32 | 1/2/2005 | 1/31/2015 |
Z294605 | DAVID | TERM | 1 | 11.5 | 11/5/2003 | 6/10/2005 |
Here's what I tried:
DATA MONTHEND1;
SET HR9;
HIREDATE2=MONTH(HIREDATE);
IF '31DEC2000'D <HIREDATE< '1JAN2009'D OR '1JAN2001'D <TERMDATE< '31JAN2999'D THEN DO WHILE (MONTH(HIREDATE2)<='1JAN2009'D);
HIREDATE2+1;
OUTPUT;
END;
RUN;
Want it to kind of look like this:
w | FIRST | RECTYPE | STORE | RATE | HIREDATE | TERMDATE | MONTH | YEAR |
Z126330 | LARRY | HIRE | 1 | 10.97 | 3/21/2000 | 1/31/2999 | JAN | 2001 |
Z126330 | LARRY | HIRE | 1 | 10.97 | 3/21/2000 | 1/31/2999 | FEB | 2002 |
Z126330 | LARRY | HIRE | 1 | 10.97 | 3/21/2000 | 1/31/2999 | MAR | 2003 |
.... .... ....
Have you looked at PROC EXPAND in SAS/ETS?
Hi Rick,
I think a do output would be easier for this case.
data monthend1 (drop=hiredate2);
set hr9;
month = month(hiredate);
year = year(hiredate);
output;
hiredate2 = intnx('month',hiredate,1,'begin');
do while (hiredate2 <= termdate);
month = month(hiredate2);
year = year(hiredate2);
output;
hiredate2 = intnx('month',hiredate2,1,'begin');
end;
run;
Hey Kurt,
It's almost there. Could you look my edit and see how you can help me look like that for every observation. Would I have to add first.empid?
And Kurt..that worked but how do I get it to stop until the end of 2008? Also, first obs has 14690. how do I get rid of that? Thanks again.
So if you want to limit it in time, you have to adapt
do while (hiredate2 <= termdate);
to
do while (hiredate2 <= min(termdate,'31dec2008'd));
Hi there,
It's actually suppose look like:
w | FIRST | RECTYPE | STORE | RATE | HIREDATE | TERMDATE | MONTH | YEAR |
Z126330 | LARRY | HIRE | 1 | 10.97 | 3/21/2000 | 1/31/2999 | JAN | 2001 |
Z126330 | LARRY | HIRE | 1 | 10.97 | 3/21/2000 | 1/31/2999 | FEB | 2001 |
Z126330 | LARRY | HIRE | 1 | 10.97 | 3/21/2000 | 1/31/2999 | MAR | 2001 |
..going until dec 31, 2008. And I am trying to expand it for every employee.
data have; input EMPID $ FIRST $ RECTYPE $ STORE RATE (HIREDATE TERMDATE) (: mmddyy12.); format HIREDATE TERMDATE mmddyy10.; cards; Z126330 LARRY HIRE 1 10.97 3/21/2000 1/31/2015 Z128570 BILL TERM 1 8.66 10/2/2000 3/7/2002 Z187169 LISA TERM 1 9.98 4/22/2005 11/1/2006 Z270823 TERESA HIRE 1 8 10/2/2000 1/31/2015 Z284042 SAM HIRE 1 9.32 1/2/2005 1/31/2015 Z294605 DAVID TERM 1 11.5 11/5/2003 6/10/2005 ; run; data want; set have; do i=0 to intck('month',HIREDATE,TERMDATE); temp=intnx('month',HIREDATE,i); if year(temp) in (2001:2008) then do; MONTH=put(temp,monname3.); YEAR=year(temp);output; end; end; drop i temp; run;
Xia Keshan
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.
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.