BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MalcolmCICWF
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
Reeza
Super User

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.

MalcolmCICWF
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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.

data_null__
Jade | Level 19

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.

MalcolmCICWF
Calcite | Level 5

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.

Astounding
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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