topic Re: grouping by month / year in SAS Procedures

grouping by month / year

AnnMarie — Thu, 08 May 2014 13:28:08 GMT

Please help! I have a dataset with ID, hiredate, termdate for different offices and would like to get a breakdown by month and quarter. For example I want to know the total number of hires in jan, feb, mar and then cumulative for that quarter and then for all the offices, a breakdown of hires by month.

I have sample data here.

OFFICES EmployeeID Hiredate Termdate

DC office 1001 01022013 04302013

DC office 1002 12012013 04302014

DC office 1003 02152013 03302014

DC office 1004 04162013 08202013

DC office 1005 01022013 04302014

DC office 1006 12012013 03302014

DC office 1007 03162013 08202013

DC office 1008 01022013 04302013

VT office 1015 02152013 01302014

VT office 1016 04162013 00302014

VT office 1017 01022013 05082014

VT office 1023 03162013 04302014

VT office 1024 01022013 03302014

VT office 1025 12012013 08202013

TN office 1026 02152013 01052014

TN office 1027 04162013 04302013

TN office 1028 01022013 05182014

TN office 1029 12012013 03302014

TN office 1030 03162013 08202013

Re: grouping by month / year

DBailey — Thu, 08 May 2014 13:30:30 GMT

I would move the hiredate to the first of the month and then group.

intnx('month',hiredate,0,'begin')

Re: grouping by month / year

Haikuo — Thu, 08 May 2014 13:51:37 GMT

PROC FREQ seems to be right for the job.

data have;

input OFFICES $9. EmployeeID :$ (Hiredate Termdate) (:mmddyy8.);

format Hiredate Termdate mmddyy10.;

cards;

DC office 1001 01022013 04302013

DC office 1002 12012013 04302014

DC office 1003 02152013 03302014

DC office 1004 04162013 08202013

DC office 1005 01022013 04302014

DC office 1006 12012013 03302014

DC office 1007 03162013 08202013

DC office 1008 01022013 04302013

VT office 1015 02152013 01302014

VT office 1016 04162013 01302014

VT office 1017 01022013 05082014

VT office 1023 03162013 04302014

VT office 1024 01022013 03302014

VT office 1025 12012013 08202013

TN office 1026 02152013 01052014

TN office 1027 04162013 04302013

TN office 1028 01022013 05182014

TN office 1029 12012013 03302014

TN office 1030 03162013 08202013

;

proc freq data=have ;

table hiredate /nocum nopercent;

format hiredate monyy7.;

table hiredate*offices /nocol nocum norow nopercent ;

run;

proc freq data=have;

table hiredate ;

format hiredate yyq6.;

run;

Haikuo

Re: grouping by month / year

AnnMarie — Thu, 08 May 2014 14:23:30 GMT

Thank you Haikuo.

How will i do this if I had this sample data as a sas dataset. Also, the hire date variable is in the char format of "01022013".

I appreciate your help!!

Re: grouping by month / year

Haikuo — Thu, 08 May 2014 14:33:24 GMT

1. If your data is in SAS table, then just go ahead use its name (with proper libref) in the Proc FREQ.

2. If your dates come in as Char, then you need to convert them into date variable before using it, such as:

data _null_;

char="01022013";

date=input(char,mmddyy8.);

put date= date9.;

run;

So you need to create a new variable to replace the Char one.

Haikuo

Re: grouping by month / year

AnnMarie — Thu, 08 May 2014 16:42:17 GMT

Awesome! Thanks!