Yearly conditional sum in SAS

[Rookie_123]

I have a below table

+------+------+------+------+------+-----+
    |  Yr  | col1 | col2 | col3 | col4 | PQR |
    +------+------+------+------+------+-----+
    | 2012 |    1 |    0 |    1 |    1 |   2 |
    | 2012 |    0 |    1 |    0 |    0 |   4 |
    | 2013 |    1 |    1 |    1 |    1 |   6 |
    | 2014 |    0 |    0 |    0 |    0 |   8 |
    | 2012 |    1 |    0 |    1 |    1 |   7 |
    | 2013 |    0 |    1 |    0 |    0 |   3 |
    | 2014 |    1 |    0 |    1 |    1 |   2 |
    | 2012 |    0 |    1 |    0 |    0 |  10 |
    | 2014 |    0 |    0 |    1 |    0 |  12 |
    | 2014 |    0 |    0 |    0 |    0 |   5 |
    +------+------+------+------+------+-----+

The output I want is as below

+------+-------+------+------+------+
    |      | Total | 2012 | 2013 | 2014 |
    +------+-------+------+------+------+
    | col1 |    17 |    9 |    6 |    2 |
    | col2 |    23 |   14 |    9 |    0 |
    | col3 |    29 |    9 |    6 |   14 |
    | col4 |    17 |    9 |    6 |    2 |
    +------+-------+------+------+------+

For row `col1` in my output table 

The column `Total` is `SUM(PQR)` when `col1` is 1 my input table

The value `17` is `SUM(PQR)` when `col1` is 1 in my input table

The value in col `2012` is `SUM(PQR)` when `col1` is 1 and `Yr=2012` in my input table

The value `9` is `SUM(PQR)` when `col1` is 1 and `Yr=2012` in my input table

Similarly `6` in column `2013` is `SUM(PQR)` when `col1` is 1 and `Yr` is `2013`

Hope the process to get output table is understood

Any help will be appreciated

[VDD]

 redone below in a datastep:

[Rookie_123]

Thanks but I wanted it in SAS 

[VDD]

the code I provided is SAS code.  Try it

Please explain @Rookie_123 what do you mean "wanted it in SAS"

[Rookie_123]

I wanted to avoid PROC SQL part.Do it entirely in SAS without using PROC SQL at all

[VDD]

Proc SQL is SAS.

Do you mean you want to do the process in a datastep?

data have;
input year col1 col2 col3 col4 pqr;
cards;
2012 1 0 1 1 2
2012 0 1 0 0 4
2013 1 1 1 1 6
2014 0 0 0 0 8
2012 1 0 1 1 7
2013 0 1 0 0 3
2014 1 0 1 1 2
2012 0 1 0 0 10
2014 0 0 1 0 12
2014 0 0 0 0 5
;

proc sort data=have out=havesort;
	by year;
run;

data have2(drop=total1 total2 total3 total4 pqr);
	retain total1 total2 total3 total4 0;
	set havesort;
	by year;
	if col1>0 then total1 + pqr;
	if col2>0 then total2 + pqr;
	if col3>0 then total3 + pqr;
	if col4>0 then total4 + pqr;
	if last.year then
		do;
			col1=total1;
			col2=total2;
			col3= total3;
			col4=total4;
			output;
			total1=0;
			total2 =0;
			total3 =0;
			total4 =0;
		end;
run;

proc transpose data=have2 out=have3;
id year;
run;

data want;
	set have3;
	total = sum(_2012,_2013,_2014);
run;

using a datastep

[Jagadishkatam]

The code may not be the best but could be helpful

data have;
input   Yr$  col1 col2 col3 col4 PQR ;
cards; 
 2012    1    0    1    1   2 
 2012    0    1    0    0   4 
 2013    1    1    1    1   6 
 2014    0    0    0    0   8 
 2012    1    0    1    1   7 
 2013    0    1    0    0   3 
 2014    1    0    1    1   2 
 2012    0    1    0    0  10 
 2014    0    0    1    0  12 
 2014    0    0    0    0   5 
 ;

data have;
set have;
output;
yr='Total';
output;
run;

proc sort data=have;by yr;run;

data sum;
set have;
by yr;
array newcol1(*) col1 col2 col3 col4;
array newcol2(*) col_1 col_2 col_3 col_4;
do i = 1 to dim(newcol1);
if newcol1(i)=1 then newcol2(i)=pqr;
end;
drop col1 col2 col3 col4;
run;

proc sql;
create table sum2 as select yr,sum(col_1) as col1, 
sum(col_2) as col2, 
sum(col_3) as col3, 
sum(col_4) as col4 from sum group by yr;
quit;

proc transpose data=sum2 out=want;
id yr;
var col1 col2 col3 col4 ;
run;

[mkeintz]

If what you really want is a report rather than a data set, then it is well worth learning about proc tabulate:

proc tabulate data=have;
  class year;    /*classifiers*/
  var col1 col2 col3 col4;  /* "analysis vars (to enable a SUM request)"*/
  weight pqr;
  tables 
    col1 col2 col3 col4                        /*rows */
    ,
    (all='Total' year=' ')*sum=' '*f=comma8.0  /*columns*/
  /rts=10 misstext='0';
run;

The "weight" statement identifies a variable to be used as a weight in calculating sums, means, variances, when applied to "analysis" variables (COL1 .. COL4 as specified in the VAR statement).  It has nothing to do with CLASS variables (year).  So the TABLES statement asks for:

1. One row for each COL variable      (the row specification precedes the comma)
2. Then (after a comma) specifiy the columns as:
   1. ALL (the entire dataset, i.e. all years), followed by a column for each year
   2. Populate the cell with sums  (of the respective weighted analysis variables)
3. All the =' ' just applies labels to appear in the table.
   
   Edited addition: You can remove all the ='some text'  or ='' to demonstrate that all they do is place (or remove) text in the printed table.  In the case of text removal (i.e. ='') a cell can actually be removed.
4. The "f=comma8.0" specifies the format to be applied within each cell
5. you can look up the rest

At first, proc tabulate may not seem intuitive, but once you grasp the syntax, it's a powerful tool.

[novinosrin]

Never ever enjoyed reading the tabulate doc , your notes rather made me comprehend, enjoy and pay attention. Very neat and sound. Gracias!

[s_lassen]

Here's how I would do it:

%let first_year=2012;
%let last_year=2014;
proc transpose data=have out=long;
  var col1-col4;
  by year pqr notsorted;
run;
  
proc sort data=long;
  by _name_;
run;

data want;
  do until(last._name_);
    set long;
    by _name_;
	total+pqr*col1;
    array years(&first_year:&last_year) year&first_year-year&last_year;
    years(year)+pqr*col1;
    end;
  output;
  call missing(total,of years(*));
  keep _name_ total year&first_year-year&last_year; 
run;

[PaigeMiller]

Since you already have the zeros and ones, using PROC MEANS/PROC SUMMARY will compute the conditional sums easily using a WEIGHT statement. For example, using COL1:

proc summary data=have;
    class year;
    var pqr/weight=col1;
    output out=sums sum=sum_pqr;
run;

No need to write your own code to do conditional sums.

[ballardw]

Are you implying that similar rules are involved with col2 col3 and col4?

Here's another approach

data have;
   input yr col1-col4 pqr;
datalines;
     2012     1     0     1     1    2 
     2012     0     1     0     0    4 
     2013     1     1     1     1    6 
     2014     0     0     0     0    8 
     2012     1     0     1     1    7 
     2013     0     1     0     0    3 
     2014     1     0     1     1    2 
     2012     0     1     0     0   10 
     2014     0     0     1     0   12 
     2014     0     0     0     0    5 
run;

data trans;
   set have;
   array c col1-col4;
   length name $ 10;
   do i=1 to dim(c);
      weightvar = c[i];
      name = vname(c[i]);
      output;
   end;
   keep yr pqr weightvar name;
run;

proc tabulate data=trans;
   class yr name;
   var pqr;
   weight weightvar;
   table name*pqr=''*sum=''*f=best5.,
         (all='Total' yr='')
         /row=float
   ;
run;
         

Your variables col1 through col4 make me suspect that perhaps proc transpose was used in creating the data. Perhaps that prior step wasn't needed for this output in that case.

[Rookie_123]

Everyone has helped in their own way to achieve what I want.Thanks for your help