Add additional variables and assign the values from another rows

[ggfggrr]

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

[gamotte · Accepted solution]

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;

[ggfggrr]

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

[gamotte]

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

[FreelanceReinh]

Or modify the increments as follows:

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

[ggfggrr]

Thank you so much for your help.

[ggfggrr]

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,

[FreelanceReinh]

---

@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" ).

[data_null__]

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

[Ksharp]

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;