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,312

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.

Esteemed Advisor
Posts: 6,307

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

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

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.

Esteemed Advisor
Posts: 6,307

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.

Grand Advisor
Posts: 9,463

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
  • 402 views
  • 0 likes
  • 4 in conversation