topic Re: Expanding Data in Statistical Procedures

Expanding Data

ali_hash — Thu, 20 Aug 2015 04:54:35 GMT

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

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

Re: Expanding Data

Rick_SAS — Thu, 20 Aug 2015 11:39:16 GMT

Have you looked at PROC EXPAND in SAS/ETS?

SAS/ETS(R) 14.1 User's Guide

Re: Expanding Data

Kurt_Bremser — Thu, 20 Aug 2015 12:20:42 GMT

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;

Re: Expanding Data

ali_hash — Thu, 20 Aug 2015 15:59:26 GMT

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.

Re: Expanding Data

ali_hash — Thu, 20 Aug 2015 16:09:48 GMT

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?

Re: Expanding Data

ali_hash — Thu, 20 Aug 2015 16:10:58 GMT

Hi Rick,

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

Re: Expanding Data

ali_hash — Thu, 20 Aug 2015 16:18:32 GMT

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.

Re: Expanding Data

Ksharp — Fri, 21 Aug 2015 03:37:59 GMT

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

Re: Expanding Data

Kurt_Bremser — Fri, 21 Aug 2015 06:21:29 GMT

So if you want to limit it in time, you have to adapt

do while (hiredate2 <= termdate);

do while (hiredate2 <= min(termdate,'31dec2008'd));