BookmarkSubscribeRSS Feed
Rookie_123
Fluorite | Level 6

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

 

12 REPLIES 12
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

 

 redone below in a datastep:

 

 

 

 

 

 

 

Rookie_123
Fluorite | Level 6

Thanks but I wanted it in SAS 

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

the code I provided is SAS code.  Try it

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

Rookie_123
Fluorite | Level 6

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

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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
Amethyst | Level 16

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;
Thanks,
Jag
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
novinosrin
Tourmaline | Level 20

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

s_lassen
Meteorite | Level 14

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
Diamond | Level 26

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.

--
Paige Miller
ballardw
Super User

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
Fluorite | Level 6

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1627 views
  • 6 likes
  • 8 in conversation