Search code examples
sasaddition

SAS: Calculating(add, subtract, multipy, divide) values in a dataset

I would like to calculate values in a dataset. So if my dataset looks like

obs  x   y
1   10  100
2   20  200
3   30  300
4   40  400

I would like to create a new dataset that calculates only the first and last row of values in the original dataset. So for column x, I would like to add 10+40=50, and for column y, I would like to add 100+400=500. So my output dataset looks like

     x   y
sum  50  500

I am not sure how to extract specific cells from the dataset and sum(or multiply) them. Thank you in advance.

Solution

  • You can use the _N_=1 condition to identify the first row of data being read with a SET statement. Additionally the END= option is used to test for last row. Store the values from the first row in retained variables that will be used in the computation performed when the last row is read.

    Example:

    data have;
    input x   y;
    datalines;
10  100
20  200
30  300
40  400
;

data want(keep=x y label='Sum from values in first and last row of have');
    set have end=lastrow_flag;

    retain x_firstrow y_firstrow;

    if _n_ = 1 then do;
        x_firstrow=x; 
        y_firstrow=y; 
    end;

    if lastrow_flag then do;
        x = sum (x_firstrow, x);
        y = sum (y_firstrow, y);
        OUTPUT;
    end;
run;

    enter image description here

    Pro Tip:

    If you are running PC SAS (Display Manager) you can program LOG window keys to perform custom commands (Use KEYS command to enter the command). I always program CTL RMB as VT &SYSLAST so that Control-Right mouse button whilst hovering over LOG window will raise the ViewTable Viewer for the last created data set.

    enter image description here