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
I would move the hiredate to the first of the month and then group.
intnx('month',hiredate,0,'begin')
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
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!!
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
Awesome! Thanks!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.