topic Re: Add additional variables and assign the values from another rows in SAS Programming

Add additional variables and assign the values from another rows

ggfggrr — Mon, 01 Oct 2018 09:21:04 GMT

I am trying to create another 2 variables in my table using PROC SQL statements.

I have a table as following;

Month ID bonus

201701 101 1000

201701 102 1050

201701 103 1020

201702 101 1550

201702 102 1560

201702 103 1580

201703 101 1750

201703 102 7050

201703 103 1600

….

And I need 2 more additional columns for each row in which I wish to have the bonus value from my next month and 2nd next month (Bonus, Bonus for next month and Bonus for 2nd next month in a single row). And I am trying to convert the above table as following;

Month ID Bonus Bonus+1 Bonus+2

201701 101 1000 1550 1750

201701 102 1050 1560 7050

201701 103 1020 1580 1600

...

....

After going through several forums, I could not find way to do it. Can anyone knows the efficient way to do it?

Thanks

Re: Add additional variables and assign the values from another rows

gamotte — Mon, 01 Oct 2018 09:36:22 GMT

Hello,

data have;
    input month id bonus;
    cards;
201701 101 1000
201701 102 1050
201701 103 1020
201702 101 1550
201702 102 1560
201702 103 1580
201703 101 1750
201703 102 7050
201703 103 1600    
;
run;

proc sql noprint;
    CREATE TABLE want AS
    SELECT a.*, b.bonus AS bonus1, c.bonus AS bonus2
    FROM have a
    LEFT JOIN have b
    ON b.ID=a.ID AND b.Month=a.Month+1 
    LEFT JOIN have c
    ON c.ID=a.ID AND c.Month=a.Month+2
    ORDER BY Month, ID 
    ;
quit;

Re: Add additional variables and assign the values from another rows

data_null__ — Mon, 01 Oct 2018 09:39:09 GMT

SQL may not be the best solution for this problem. PROC TRANSPOSE can do this more easily.

Re: Add additional variables and assign the values from another rows

Ksharp — Mon, 01 Oct 2018 10:27:55 GMT

data have;
    input month : yymmn6. id bonus;
    format month yymmn6.;
    cards;
201701 101 1000
201701 102 1050
201701 103 1020
201702 101 1550
201702 102 1560
201702 103 1580
201703 101 1750
201703 102 7050
201703 103 1600    
;
run;

proc sql;
select  *,
(select bonus from have where id=a.id and month=intnx('month',a.month,1)) as bonuse1, 
(select bonus from have where id=a.id and month=intnx('month',a.month,2)) as bonuse2
 from have as a;
quit;

Re: Add additional variables and assign the values from another rows

ggfggrr — Mon, 01 Oct 2018 11:57:56 GMT

Thank you so much for your prompt reply.

Is there any way to handle the month when it is 201711 or 201712 (where the month falls as Nov or Dec?). Please note that the month values is in the numeric format.

Thanks again

Re: Add additional variables and assign the values from another rows

gamotte — Mon, 01 Oct 2018 12:18:49 GMT

Use a date format and intnx function as in @Ksharp's answer.

Re: Add additional variables and assign the values from another rows

FreelanceReinh — Mon, 01 Oct 2018 12:49:17 GMT

Or modify the increments as follows:

... =a.Month+1+88*(mod(a.Month,100)>11)
...
... =a.Month+2+88*(mod(a.Month,100)>10)

Re: Add additional variables and assign the values from another rows

ggfggrr — Mon, 08 Oct 2018 09:37:40 GMT

Thank you so much for your help.

Re: Add additional variables and assign the values from another rows

ggfggrr — Mon, 08 Oct 2018 12:14:39 GMT

Dear I understood that it works. However can you please explain how does the following work;

=a.Month+2+88*(mod(a.Month,100)>10)

For example;

I wish to add 2 months to 201711 and it should output 201801.

The reminder for mod(201711,100) is 11.

And 11>10 is TRUE

Then how it interprets is something I could not get in line with the output.

Thanks again.

Kind regards,

Re: Add additional variables and assign the values from another rows

FreelanceReinh — Mon, 08 Oct 2018 13:53:23 GMT

@ggfggrr wrote:

Dear I understood that it works. However can you please explain how does the following work;
=a.Month+2+88*(mod(a.Month,100)>10)
For example;

I wish to add 2 months to 201711 and it should output 201801.

The reminder for mod(201711,100) is 11.

And 11>10 is TRUE

... and the Boolean value TRUE is interpreted as the numeric value 1 in arithmetic expressions.

Hence, in your example you obtain

=201711+2+88*1

which equals 201801, as desired.

The general idea is to save an IF/THEN statement, IFN function call or (in PROC SQL) a CASE expression by using a Boolean expression in a calculation. In this context the Boolean expression evaluates to 1 for TRUE and 0 for FALSE and this difference can be used to switch between two numeric results. To apply this technique, you only have to develop an arithmetic expression (containing the Boolean expression) whose results switch between the two results you want to obtain depending on the Boolean value. The resulting SAS code is mostly concise, but many people will find the longer code (using IF/THEN etc.) more readable and easier to maintain. So, it's a technique rather for mathematically inclined programmers (and "code poets" ).