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

Good day All,

 

A happy new year to you all.

 

Please assist me with my inquiry:

I want to firstly calculate a new instalment value but I want to create columns so that each account entry has 1 account number with different instalment values as columns. The only instalment value that changes is the 6 plan instalment with a formula of (instalment * 0.5572)

 

This is how the data looks:

account numberplaninstalment
123456789135
1234567896322
12345678912472


The desired output:

account numberplan1plan6plan12calculated instalment1calculated instalment6calculated instalment12
123456789161235179472
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
*sort data so you have correct order for transpose;
proc sort data=have /*have is your input data set*/
              out=a; /*creates a new copy of input data that is sorted called A*/
by accountNo; 
run;

*this step flips your data to a long structure;
proc transpose data=a  /*input data set is A*/
                         out=b  /*output data set is B*/
                         prefix=installment; /*Prefix for new variables*/
   by accountNo;
    var installment; /*vars to fill inner cells of table*/
    id plan; /*variable to differentiate the column names in output table*/
run;

*do calculations;
data want; /*want is your output dataset*/
    set b; /*b is your input data set from previous step*/
	installment6=installment6*0.5572; /*desired calculation*/
run;

This a commented version that should answer all your questions. 

Please review this post to understand how PROC TRANSPOSE works.


Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

If you want to understand code, run it. Identify the input and output data sets and compare the two data sets to understand what's changed. Then look at the code and figure out what each line does. Then start changing it to fit your situation. 

EDIT: You hadn't mentioned multiple accounts and your example only had one account so the original solution provided would have worked for a single account. To incorporate multiple accounts, you really should include that from the start btw, you need to add a BY statement. See the code in red above. Note that I don't know what your variable name actually is, so you'll need to correct for that.

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

This ought to work. You have asked about CASE WHEN, but that is not needed here.

 

data a;
    input account plan installment;
	cards;
123456789	1	35
123456789	6	322
123456789	12	472
;

proc transpose data=a out=b prefix=installment;
    var installment;
	id plan;
run;

data b;
    set b;
	installment6=installment6*0.5572;
run;

 

--
Paige Miller
MagD
Quartz | Level 8

Hi Paige,

 

Thank you so much for your response.

 

I am not too familiar with the solution that you provided below. I see in the first data step, there is no 'set' step. How do I go about incorporating your solution by setting a location for where the data originates from? 

Reeza
Super User
You ignore the first data step, that's creating the raw data to work with and work from the second step. PROC TRANSPOSE restructures your data and the the last data step does the calculations you need.
MagD
Quartz | Level 8

Hi Reeza,

 

I have over 30, 000 accounts that I need to use this solution for. Is there a way around this by setting the location of the raw data to be used?

Reeza
Super User
*sort data so you have correct order for transpose;
proc sort data=have /*have is your input data set*/
              out=a; /*creates a new copy of input data that is sorted called A*/
by accountNo; 
run;

*this step flips your data to a long structure;
proc transpose data=a  /*input data set is A*/
                         out=b  /*output data set is B*/
                         prefix=installment; /*Prefix for new variables*/
   by accountNo;
    var installment; /*vars to fill inner cells of table*/
    id plan; /*variable to differentiate the column names in output table*/
run;

*do calculations;
data want; /*want is your output dataset*/
    set b; /*b is your input data set from previous step*/
	installment6=installment6*0.5572; /*desired calculation*/
run;

This a commented version that should answer all your questions. 

Please review this post to understand how PROC TRANSPOSE works.


Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

If you want to understand code, run it. Identify the input and output data sets and compare the two data sets to understand what's changed. Then look at the code and figure out what each line does. Then start changing it to fit your situation. 

EDIT: You hadn't mentioned multiple accounts and your example only had one account so the original solution provided would have worked for a single account. To incorporate multiple accounts, you really should include that from the start btw, you need to add a BY statement. See the code in red above. Note that I don't know what your variable name actually is, so you'll need to correct for that.

MagD
Quartz | Level 8

Thank you so much for all of your assistance!

Reeza
Super User
I added an edit to handle multiple accounts, please see previous post.
MagD
Quartz | Level 8

Thank you so much! It works. Thank you, thank you!!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1324 views
  • 2 likes
  • 3 in conversation