topic Re: proc transpose with continues dates in SAS Procedures

proc transpose with continues dates

Karo_22 — Mon, 18 May 2009 20:48:04 GMT

I have rearranged my data with simple proc transpose. But my catch is that one of my column headings is month/year combination.

So my data originally looked like this :
WHERE mths1....mths37 is month/year that was calculated via array so for this month mths1 is May2006, mths2= June 2006 etc until mths 37=May 2009. These mths will change every month...so in June 2009 , my mths1 =June2006, mths2=July2006...until mths37=June2009.
So my proc transpose is easy until I got to "rename" my mths1 - mths37"
This ir original data set
company mths1 mths2 mths3 etc
589 5 4 3
590 6 7 8

what I got with with a simple step
proc summary data=dataset;
var mths1-mths37;
by company;
output out=finout (DROP=_TYPE_ _FREQ_) SUM=;
company 589 company 590
mths1 5 6
mths2 4 7
mths3 3 8

What I am looking for is to have a code that will do following

company 589 company 590
May06 5 6
June06 4 7
July06 3 8
etc
May2009 2 3

and next month it would be same as above but it will start left column with June 06 & go down until June 2009.

I am not sure if this is to complicated for forum or I have to create a ticket. But maybe someone could give me some hints, little push to automize that .Thanks a lot.

Re: proc transpose with continues dates

sbb — Mon, 18 May 2009 21:05:06 GMT

Using a DATA step, setup an ARRAY referencing the range of mnths, and generate a SAS numeric (DATE) variable using the INTNX function and decrement in a DO / END loop using DIM( TO 1 BY -1. Output a new observation with each DO loop pass, thereby creating a vertical representation of your transposed data.

Scott Barry
SBBWorks, Inc.

Re: proc transpose with continues dates

sbb — Mon, 18 May 2009 21:14:49 GMT

Have a look at the SAS support http://support.sas.com/ website and using the SEARCH facility you will find SAS technical papers and also DOC references. Also, here is a Google advanced search argument (limited to the SAS.COM site) that identifies several related matches on the topic of your post:

assigning sas date variables intnx site:sas.com

Here's a few of several technical papers revealed with the Google search:

Sample 24830: Self- adjusting Date Formats
http://support.sas.com/kb/24/830.html

A Beginners Guide to SAS  Date and Time Handling
Wayne Finley, State of California HHSDC, Sacramento, CA
http://www2.sas.com/proceedings/sugi25/25/btu/25p058.pdf

Scott Barry
SBBWorks, Inc.