Help using Base SAS procedures

grouping by month / year

Reply
Occasional Contributor
Posts: 8

grouping by month / year

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

Super Contributor
Posts: 578

Re: grouping by month / year

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

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

Respected Advisor
Posts: 3,156

Re: grouping by month / year

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

Occasional Contributor
Posts: 8

Re: grouping by month / year

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!!

Respected Advisor
Posts: 3,156

Re: grouping by month / year

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

Occasional Contributor
Posts: 8

Re: grouping by month / year

Awesome! Thanks!

Ask a Question
Discussion stats
  • 5 replies
  • 335 views
  • 2 likes
  • 3 in conversation