BookmarkSubscribeRSS Feed
ali_hash
Calcite | Level 5

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

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

8 REPLIES 8
Rick_SAS
SAS Super FREQ

Have you looked at PROC EXPAND in SAS/ETS?

SAS/ETS(R) 14.1 User's Guide

ali_hash
Calcite | Level 5

Hi Rick,

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

Kurt_Bremser
Super User

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;

ali_hash
Calcite | Level 5

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?

ali_hash
Calcite | Level 5

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.

Kurt_Bremser
Super User

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

ali_hash
Calcite | Level 5

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.

Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

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.

Discussion stats
  • 8 replies
  • 2034 views
  • 0 likes
  • 4 in conversation