topic Re: Use proc tabulate to work out percentage change over time in SAS Procedures

Use proc tabulate to work out percentage change over time

IanatAIHW — Mon, 06 May 2013 05:36:58 GMT

I have annual databases I bring together to generate summaries over several years worth of data and the tables typically statement that looks like this

table anyoldvariable , year * dummy * sum ;

which gives me a table that looks like this

	2005	2006	2007	2008	2009
In the nursing labour force	253,592	254,956	277,297	282,968	291,246
Employed in Nursing	243,916	244,360	263,331	269,909	276,751
Clinical nurse	203,517	196,998	212,480	216,644	220,815
Clinical management	24,314	25,976	28,258	28,695	29,971
Lecture/teacher/educator	7,579	7,226	7,341	8,766	9,570
Researcher	2,069	1,976	2,153	2,220	2,325
Other	6,437	12,183	13,100	13,585	14,070
On extended leave	5,183	7,488	9,863	9,383	9,880
Looking for work	4,493	3,108	4,103	3,675	4,615
Looking for work - employed	1,814	1,094	1,515	1,440	1,815
- Not employed	2,678	2,014	2,588	2,235	2,801
Not in the nursing labour force	29,110	30,663	28,537	29,860	29,735
Overseas	4,546	2,081	2,047	2,315	3,233
Not looking for work	24,564	28,582	26,490	27,544	26,503
Not looking for work - employed	12,370	14,579	12,628	13,556	13,210
- not employed	12,194	14,003	13,862	13,988	13,293
Total registered and enrolled nurses	282,702	285,619	305,834	312,828	320,982
Mutiple registration	8,928	9,145	11,783	12,755	13,046
Total registrations and enrolments	291,630	294,764	317,618	325,583	334,028

In the past I just pasted into excel to calculate the percentage change between the first year and the last year but our reports are to be rewritten so no "manual interference" is required

Can proc tabulate do this or do I need to learn proc report or do a proc summary a transpose and manual calculation?

Re: Use proc tabulate to work out percentage change over time

allurai0412 — Tue, 07 May 2013 04:35:26 GMT

hi,

if you need the differnce b/n two years you can go with proc compare....

data have;
input employement &$28. @29 y_2005 comma7.@37 y_2006 comma7. @45 y_2007 comma7.;
format y_2005 comma7. y_2006 comma7. y_2007 comma7.;
datalines;
in the nursing labour force 253,592 254,956 277,297
employed in Nursing 243,916 244,360 263,331
clinical nurse 203,517 196,998 212,480
;
run;

proc compare base=have nosummary out=year_per;
var y_2005;
with y_2006;
var y_2006;
with y_2007;
run;

Regards

Allu

i think , you can go with with steps ...proc transpose ...and lag () function also ....but please check this ....

Re: Use proc tabulate to work out percentage change over time

Astounding — Tue, 07 May 2013 13:11:31 GMT

You're probably right that the best tool for the job is PROC REPORT. But you can get PROC TABULATE to do this if you perform the calculations in a DATA step. Consider having your current PROC TABULATE generate an output data set rather than a report. It is likely to be straightforward to use that output data set in a DATA step, make a few calculations, then use the result as the input to a second PROC TABULATE that actually prints the report.

Good luck.