topic Re: Proc transpose in SAS Procedures

Proc transpose

Paul_NYS — Sun, 11 Nov 2012 18:26:44 GMT

I have the below Proc Transpose statement. The Id variable, exitMonthCategory, is numeric (1, 2, 3, 4, 5, 6, 12, 18, 24, 30.....66). When I run the transpose, the columns are appearing out of order: 6, 12, 24, 30, 36, 1, 2, 3, 4, 5, 18, 54. Is there a way I can order this to appear ascending?

Paul

proc transpose data=s1age1 out=s1age2;

by cnty_name startyear agecat4 exit;

id exitMonthCategory;

run;

Re: Proc transpose

data_null__ — Sun, 11 Nov 2012 18:45:33 GMT

Something like this will work to order the value in a data driven program

data frame;
   do exitMonthCategory = 1 to 5, 6 to 66 by 6;
      output;
      end;
   run;

data s1age1V / view=s1age1v;
   set frame s1age1;
   run;
proc transpose data=s1age1v
   out=s1age2(where=(not missing(cnty_name)))

Re: Proc transpose

Paul_NYS — Sun, 11 Nov 2012 19:15:27 GMT

I don't completely understand the above code, but when I run it, I am getting the below error on the transpose statement:

ERROR: Variable cnty_name is not on file WORK.S1AGE2.

When I look at both S1AGE2 and s1age1v, cnty_name is there?

Paul

Re: Proc transpose

data_null__ — Sun, 11 Nov 2012 19:56:29 GMT

The final proc transpose is not complete. You did not supply sample data so a complete example was not possible without me creating the sample data. Show your work.

Re: Proc transpose

art297 — Sun, 11 Nov 2012 19:59:10 GMT

Paul,

Without an example dataset I can't test DN's suggestion. If you had used the code I had suggested in a previous thread, namely using the prefix option and idlabel rather than id, the data would have come out in the right order.

However, that said, since you will always have the same range of categories (which I don't know what they are but will presume they end up being something like _1 to _20), you could just add an extra datastep after your use of proc transpose. e.g.:

proc transpose data=s1age1 out=s1age2;

by cnty_name startyear agecat4 exit;

id exitMonthCategory;

run;

data s1age2;

retain cnty_name startyear agecat4 exit _1-_20;

set s1age2;

run;

Re: Proc transpose

Paul_NYS — Sun, 11 Nov 2012 20:09:19 GMT

The problem with prefix is that is just creates columns 1->17, printing: 'month1', 'month'2, 'month3', etc. My columns are actually: 1, 2, 3, 4, 5, 6, 12, 18, 24, 30, 36.........66. They need to be printed as is. The above will also print sequential columns 1-20.

Is there a way to print what is actually there?

Paul

Re: Proc transpose

Paul_NYS — Sun, 11 Nov 2012 20:22:11 GMT

I don't know how to attach files to the posting, but below is a link to a zip file containing data:

http://www.nycourts.gov/surveys/cwcip/agerange-posting2.zip

Re: Proc transpose

art297 — Sun, 11 Nov 2012 21:03:39 GMT

Paul,

Hopefully someone else can provide a more straight-forward way of doing this but, based on your original sample data and a very limited number of groupings (they would have to be expanded of course), the following is one way to achieve what I think you want:

proc format;

value timespan

low-0.0834='1 Month'

0.0835-0.167='2 Months'

0.168-0.2501='3 Months'

other='> 3 Months';

run;

data have;

informat cnty_name $20.;

informat description $30.;

informat start_date end_date date9.;

format start_date date9.;

input id cnty_name description agecat4 year start_date end_date;

exitMonthCategory=yrdif(start_date, end_date, 'AGE');

cards;

2 Hopeland Outcome-2940 1 2006 8feb2012 11nov2012

1 Hopeland Outcome-2940 1 2006 8jan2012 11nov2012

3 Hopeland Outcome-2940 2 2006 8aug2012 11nov2012

5 Hopeland Outcome-2940 2 2006 8oct2012 11nov2012

4 Hopeland Outcome-2940 2 2006 8sep2012 11nov2012

1 Nohopeland Outcome-2940 1 2006 8jan2012 11nov2012

2 Nohopeland Outcome-2940 1 2006 8feb2012 11nov2012

3 Nohopeland Outcome-2940 3 2006 8aug2012 11nov2012

4 Nohopeland Outcome-2940 3 2006 8sep2012 11nov2012

5 Nohopeland Outcome-2940 3 2006 12oct2012 11nov2012

;

proc freq data=have noprint;

tables cnty_name*agecat4*exitMonthCategory/out=test;

format exitMonthCategory timespan.;

run;

options validvarname=any;

proc transpose data=test out=want (drop=_label_

rename=(_name_=DataElement));

by cnty_name agecat4;

id exitMonthCategory;

run;

data want;

retain cnty_name agecat4 DataElement

'1 Month'n

'2 Months'n

'3 Months'n

'> 3 Months'n;

set want;

run;

Re: Proc transpose

data_null__ — Mon, 12 Nov 2012 11:47:37 GMT

What is the variable to be transposed?

data s1age1;
   infile cards expandtabs;
   input cnty_name:$10.   agecat4 exit:$3.   startyear exitMonthCategory;
   cards;
County-XXTT 4 XRF   2007 5
County-XXTT 4 XRF   2007 4
County-XXTT 3 ZTC   2009 42
County-XXTT 3 ZTC   2009 42
County-XXTT 4 ZTC   2010 30
County-XXTT 4 ZTC   2009 42
County-XXTT 4 XRF   2009 30
County-XXTT 3 XRF   2009 30
County-XXTT 4 XRF   2011 1
County-XXTT 3 XRF   2006 18
County-XXTT 3 XRF   2006 5
County-XXTT 4 XRF   2006 12
County-XXTT 4 ZTC   2007 66
County-XXTT 4 ZTC   2008 54
County-XXTT 3 XRF   2007 24
County-XXTT 2 XRF   2007 24
County-XXTT 3 ZTC   2010 24
County-XXTT 3 ZTC   2010 24
County-XXTT 3 XOT   2008 54
County-XXTT 4 XRF   2008 2
County-XXTT 2 XRF   2006 12
County-XXTT 3 XRF   2009 12
County-XXTT 3 XRF   2011 1
County-XXTT 3 XRF   2008 6
County-XXTT 4 ZTC   2010 30
County-XXTT 4 XRF   2007 2
County-XXTT 3 XRF   2006 2
County-XXTT 3 XRF   2006 1
County-XXTT 2 XRF   2006 1
County-XXTT 3 XRF   2008 1
County-XXTT 4 XRF   2010 1
County-XXTT 4 XRF   2007 2
County-XXTT 3 XRF   2007 12
County-XXTT 4 XRF   2009 42
County-XXTT 4 XRF   2009 18
;;;;
   run;

data frame;
   if 0 then set s1age1(keep=exitMonthCategory);
   do exitMonthCategory = 1 to 5, 6 to 66 by 6;
      output;
      end;
   run;

data s1age1V / view=s1age1v;
   set frame s1age1;
   run;
proc transpose data=s1age1v
   out=s1age2(where=(not missing(cnty_name)));
   by cnty_name agecat4   exit startyear notsorted;
   id exitMonthCategory;
   var exitMonthCategory /*?*/;
   run;

Re: Proc transpose

Paul_NYS — Mon, 12 Nov 2012 14:17:35 GMT

Yes, the transposed variable is exitMonthCategory . However, when I ran this code, I am getting the below errors.

27	by cnty_name agecat4 exit startyear notsorted;
28	id exitMonthCategory;
29	var exitMonthCategory /?/;
30	run;

ERROR: The ID value "'60'n" occurs twice in the same BY group.