Programming the statistical procedures from SAS

Expanding Data

Reply
Occasional Contributor
Posts: 10

Expanding Data

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.

EMPIDFIRSTRECTYPESTORERATEHIREDATETERMDATE
Z126330LARRYHIRE110.973/21/20001/31/2015
Z128570BILLTERM18.6610/2/20003/7/2002
Z187169LISATERM19.984/22/200511/1/2006
Z270823TERESAHIRE1810/2/20001/31/2015
Z284042SAMHIRE19.321/2/20051/31/2015
Z294605DAVIDTERM111.511/5/20036/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:

wFIRSTRECTYPESTORERATEHIREDATETERMDATEMONTHYEAR
Z126330LARRYHIRE110.973/21/20001/31/2999JAN2001
Z126330LARRYHIRE110.973/21/20001/31/2999FEB2002
Z126330LARRYHIRE110.973/21/20001/31/2999MAR2003

....                                                                                                              ....           ....

SAS Super FREQ
Posts: 3,839

Re: Expanding Data

Have you looked at PROC EXPAND in SAS/ETS?

SAS/ETS(R) 14.1 User's Guide

Occasional Contributor
Posts: 10

Re: Expanding Data

Hi Rick,

I think a do output would be easier for this case.

Super User
Posts: 8,369

Re: Expanding Data

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

Re: Expanding Data

Posted in reply to KurtBremser

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?

Occasional Contributor
Posts: 10

Re: Expanding Data

Posted in reply to KurtBremser

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.

Super User
Posts: 8,369

Re: Expanding Data

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));

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

Re: Expanding Data

Hi there,

It's actually suppose look like:

wFIRSTRECTYPESTORERATEHIREDATETERMDATEMONTHYEAR
Z126330LARRYHIRE110.973/21/20001/31/2999JAN2001
Z126330LARRYHIRE110.973/21/20001/31/2999FEB2001
Z126330LARRYHIRE110.973/21/20001/31/2999MAR2001

..going until dec 31, 2008. And I am trying to expand it for every employee.

Super User
Posts: 10,214

Re: Expanding Data

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

Ask a Question
Discussion stats
  • 8 replies
  • 473 views
  • 0 likes
  • 4 in conversation