topic Calculation in Case When Statement in New SAS User

Calculation in Case When Statement

MagD — Tue, 14 Jan 2020 15:33:11 GMT

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 number	plan	instalment
123456789	1	35
123456789	6	322
123456789	12	472

The desired output:

account number	plan1	plan6	plan12	calculated instalment1	calculated instalment6	calculated instalment12
123456789	1	6	12	35	179	472

Re: Calculation in Case When Statement

PaigeMiller — Tue, 14 Jan 2020 15:47:36 GMT

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;

Re: Calculation in Case When Statement

MagD — Tue, 14 Jan 2020 16:37:12 GMT

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?

Re: Calculation in Case When Statement

Reeza — Tue, 14 Jan 2020 16:45:33 GMT

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.

Re: Calculation in Case When Statement

MagD — Tue, 14 Jan 2020 17:00:00 GMT

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?

Re: Calculation in Case When Statement

Reeza — Tue, 14 Jan 2020 17:13:33 GMT

*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.

Re: Calculation in Case When Statement

MagD — Tue, 14 Jan 2020 17:11:21 GMT

Thank you so much for all of your assistance!

Re: Calculation in Case When Statement

Reeza — Tue, 14 Jan 2020 17:13:56 GMT

I added an edit to handle multiple accounts, please see previous post.

Re: Calculation in Case When Statement

MagD — Tue, 14 Jan 2020 17:20:31 GMT

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