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.
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.
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.
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.
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.
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.
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.
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.