How to sum two columns based on another column condition in SAS and store the result in a variable

1
votes

I have data like this :

ColumnA  ColumnB ColumnC
1        10       20
1        10       30
0        50       10
0        50       20
1        10       40

I want to sum values of ColumnB and ColumnC where the values of ColumnA = 1 and store the result in a variable i.e. I want 120 (sum values of ColumnB and ColumnC where the values of ColumnA = 1) and store this result in a variable in SAS.

With this I also want to (sum values of ColumnB and ColumnC where the values of ColumnA = 0) in another variable i.e. 130 in another variable in SAS

I tried to create a variable in proc print, means,etc. and even thought of doing it in proc sql but was unable to achieve the result.

1
sqlsas

1 Answers

0
votes

Easily done with basic SQL:

data have;
infile datalines;
input columnA columnB columnC;
datalines;
1 10 20
1 10 30
0 50 10
0 50 20
1 10 40
;
run;

proc sql;
  select sum(ColumnB)+sum(ColumnC)
  into: want
  from have
  where columnA=1
  ;
quit;

/* the result is stored in variable &want */

%put &want.;

EDIT: to answer your follow-up question, this will give you two output variables with the two sums:

data have;
infile datalines;
input columnA columnB columnC;
datalines;
1 10 20
1 10 30
0 50 10
0 50 20
1 10 40
;
run;

proc sql;
  select sum(case when columnA=1 then ColumnB+ColumnC end) as A0
        ,sum(case when columnA=0 then ColumnB+ColumnC end) as A1
  into: want0, :want1
  from have
  ;
quit;

/* variable &want1 contains the result where ColumnA=1 and &want2 where ColumnA=0 */
%put &want0;
%put &want1;