topic Re: Yearly conditional sum in SAS in SAS Programming

Yearly conditional sum in SAS

Rookie_123 — Sun, 28 Oct 2018 13:05:49 GMT

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

Re: Yearly conditional sum in SAS

VDD — Sun, 28 Oct 2018 17:24:17 GMT

redone below in a datastep:

Re: Yearly conditional sum in SAS

Rookie_123 — Sun, 28 Oct 2018 13:57:37 GMT

Thanks but I wanted it in SAS

Re: Yearly conditional sum in SAS

VDD — Sun, 28 Oct 2018 14:05:45 GMT

the code I provided is SAS code. Try it

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

Re: Yearly conditional sum in SAS

Rookie_123 — Sun, 28 Oct 2018 14:09:11 GMT

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

Re: Yearly conditional sum in SAS

VDD — Sun, 28 Oct 2018 15:06:47 GMT

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

Re: Yearly conditional sum in SAS

Jagadishkatam — Sun, 28 Oct 2018 17:58:25 GMT

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;

Re: Yearly conditional sum in SAS

mkeintz — Mon, 29 Oct 2018 04:57:30 GMT

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:

One row for each COL variable (the row specification precedes the comma)
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)
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.
The "f=comma8.0" specifies the format to be applied within each cell
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.

Re: Yearly conditional sum in SAS

novinosrin — Mon, 29 Oct 2018 01:23:29 GMT

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

Re: Yearly conditional sum in SAS

s_lassen — Mon, 29 Oct 2018 11:17:37 GMT

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;

Re: Yearly conditional sum in SAS

PaigeMiller — Mon, 29 Oct 2018 12:17:01 GMT

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.

Re: Yearly conditional sum in SAS

ballardw — Mon, 29 Oct 2018 17:54:51 GMT

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.

Re: Yearly conditional sum in SAS

Rookie_123 — Thu, 01 Nov 2018 12:04:10 GMT

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