topic Re: Another Proc_Transpose Question in SAS Procedures

Another Proc_Transpose Question

MalcolmCICWF — Fri, 23 Aug 2013 19:48:14 GMT

I am new to SAS and my skills are still abysmal at best. I am trying to work my way through Proc Transpose and I see frequent postings on it here in the forum. I was hoping someone could help walk me through my task so I better understand the Proc Transpose procedure. My data set that I am working with is as shown below.

Just observing the highlighted record, I am trying to transpose the Month_YY columns by each Key variable. The 0's represent the payment for each month of that key and I want the months in a single column with each paymnet in the next column, all while keeping the rest of the record constant. I have an example below of what I am trying to achieve.

Re: Another Proc_Transpose Question

Reeza — Fri, 23 Aug 2013 20:02:16 GMT

Assuming your data is sorted I'd probably go with a datastep. The link below is a good instruction. If you really want proc transpose that link is further below.

SAS Learning Module: Reshaping wide to long using a data step

You can use the vname function to capture the name of the array variable.

eg.

do i = 1 to dim(month_vars);

value=month_vars(i);

month=vname(month_vars(i));

output;

end;

SAS Learning Module: How to reshape data wide to long using proc transpose

The trick is to keep all the variables you want in your BY statement.

Re: Another Proc_Transpose Question

MalcolmCICWF — Fri, 23 Aug 2013 20:15:46 GMT

The data I have in my first example is already from a proc import of a csv file spit out form another sas job, so that is the format and structure I have to work with. I read that document and cannot get it to apply correctly to what I am working on. I was hoping someone could supply me some code to help me achieve my goal. I would be able to understand what it is doing much better I had an example of the code that provided my results.

Re: Another Proc_Transpose Question

Tom — Fri, 23 Aug 2013 20:19:23 GMT

proc transpose ;

by facevalue proceeds poolstart key portfpool trantype ;

run;

The variable _NAME_ will have the former variable names.

The variable COL1 will have the values.

Re: Another Proc_Transpose Question

Astounding — Fri, 23 Aug 2013 20:22:09 GMT

Just a side piece of advice. There are many more important areas to start learning SAS than PROC TRANSPOSE. Just to name a few, study these statements within a DATA step (what they do, when they can be used):

RETAIN

value + amount;

LENGTH

The list could be longer, but each of these will likely prove more valuable to you than learning PROC TRANSPOSE.

Good luck.

Re: Another Proc_Transpose Question

data_null__ — Fri, 23 Aug 2013 20:24:04 GMT

Once you get the data transposed as per Tom's suggestion you will want to read the _NAME_ field using a date informat so you can convert the month and year to SASDate.

Re: Another Proc_Transpose Question

MalcolmCICWF — Fri, 23 Aug 2013 20:36:37 GMT

Thanks Tom, adding all my variables to be sorted and in the order I need them was the piece I was missing in all of this. I was only using a single variable to sort by which was throwing all my data off.