Help using Base SAS procedures

Another Proc_Transpose Question

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Another Proc_Transpose Question

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.

Untitled.jpg

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.

Capture.JPG


Accepted Solutions
Solution
‎08-23-2013 04:19 PM
Super User
Super User
Posts: 6,502

Re: Another Proc_Transpose Question

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.

View solution in original post


All Replies
Super User
Posts: 17,960

Re: Another Proc_Transpose Question

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.

Occasional Contributor
Posts: 8

Re: Another Proc_Transpose Question

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.

Solution
‎08-23-2013 04:19 PM
Super User
Super User
Posts: 6,502

Re: Another Proc_Transpose Question

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.

Respected Advisor
Posts: 3,777

Re: Another Proc_Transpose Question

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.

Occasional Contributor
Posts: 8

Re: Another Proc_Transpose Question

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.

Super User
Posts: 5,099

Re: Another Proc_Transpose Question

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

BY

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 171 views
  • 4 likes
  • 5 in conversation