Sum of variable from 2 different proc statements with where condition for that variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Sum of variable from 2 different proc statements with where condition for that variable

Hello all, excuse me if this is a silly or really easy question, because I am really new to SAS.

 

I have 2 proc statements:

PROC PRINT DATA=WORK.IMPORT;
	WHERE DonColct="yes";
	
	TITLE1 'Earth Day Festival';
	TITLE3 'Sierra Club';
	TITLE4 'Apex High School Chapter';
	TITLE6 'Full Event Report';
	TITLE8 'Pledge Collected=yes';
	
	SUM donamt;
	
	VAR Sponsor donamt;
RUN;

PROC PRINT DATA=WORK.IMPORT;
	WHERE DonColct="no";
	
	TITLE1 'Earth Day Festival';
	TITLE3 'Sierra Club';
	TITLE4 'Apex High School Chapter';
	TITLE6 'Full Event Report';
	TITLE8 'Pledge Collected=no';
	
	SUM donamt;
	
	VAR Sponsor donamt;
RUN;

Inside the second proc statement, I want to add the sum of the donamt variable for all observations in the data set, not just when DonColct = "yes". How can this be done?


Accepted Solutions
Solution
‎03-08-2017 11:20 PM
PROC Star
Posts: 7,468

Re: Sum of variable from 2 different proc statements with where condition for that variable

Posted in reply to NewbieToSAS

You also left one important semi-colon off. Try the following:

 

data v_need/view=v_need;
	set work.import (where=(DonColct='no'))
              work.import (where=(DonColct='yes')) ;
run;

proc print data=v_need;
	FORMAT donamt dollar11.2;
	
	sumlabel='#byval(DonColct) Total';
	grandtotal_label='Grand Total';
	
	TITLE1 'Earth Day Festival';
	TITLE3 'Sierra Club';
	TITLE4 'Apex High School Chapter';
	TITLE6 'Full Event Report';
	TITLE8 'Pledge Collected=yes';
	SUM donamt;
	
	VAR Sponsor donamt;
	by DonColct;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
PROC Star
Posts: 7,468

Re: Sum of variable from 2 different proc statements with where condition for that variable

Posted in reply to NewbieToSAS

Why not just include them within one proc print? e.g.:

 

proc sort data=sashelp.class out=class;
  by sex;
run;
proc print data=class
   sumlabel='#byval(sex) Total'
   grandtotal_label='Grand Total';;
  title1 'whatever';
  title2 'etc.';
   sum weight;
  var name weight;
  by sex;
run;

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 9

Re: Sum of variable from 2 different proc statements with where condition for that variable

Actually, the thing is, I need 2 different tables as output, one with the data where DonColct="No" and the other where DonColct="Yes". The second table should have the sum of donamt for (where DonColct="No") and the overall sum of donamt just below it.

 

The second table should look like below. The first shaded box under "Donation Amount Pledged" is the total for donamt where DonColct="No" and the second shaded box under "Donation Amount Pledged" is the grand total of donamt:

 

Capture.PNG

 

While the first table should look like (This works fine):

 

Capture.PNG

Trusted Advisor
Posts: 1,018

Re: Sum of variable from 2 different proc statements with where condition for that variable

Or, given you know in advance all the by levels, you can avoid sorting and writing the intemediate data set to disk.  Instead you could feed @art297's proc print with a data set VIEW (not data set FILE) pre-sorted by sex.

 

data v_need/view=v_need;
  set sashelp.class (where=(sex='F'))
      sashelp.class (where=(sex='M')) ;
run;

proc print data=v_need    .... rest of Art's code here 
Occasional Contributor
Posts: 9

Re: Sum of variable from 2 different proc statements with where condition for that variable

Thanks for the help guys, sorry to say I'm still facing a problem :-(

 

Mkeintz, I tried merging your code with Art297's, however, it seems to produxe an error message at the log statement saying:

 75         sumlabel='#byval(DonColct) Total'
             ___
             180
 ERROR 180-322: Statement is not valid or it is used out of proper order.

This the assimilated form of the code I tried running as a standalone:

 

data v_need/view=v_need;
	set work.import (where=(DonColct='yes'))
		work.import (where=(DonColct='no')) ;
run;

proc print data=v_need;
	FORMAT donamt dollar11.2;
	
	sumlabel='#byval(DonColct) Total'
	grandtotal_label='Grand Total';;
	
	TITLE1 'Earth Day Festival';
	TITLE3 'Sierra Club';
	TITLE4 'Apex High School Chapter';
	TITLE6 'Full Event Report';
	TITLE8 'Pledge Collected=yes';
	SUM donamt;
	
	VAR Sponsor donamt;
	by DonColct;
run;

Can you please point out where I went wrong? Actually, I'm really new to SAS, so I've not actually familiar with some of the lines in this code (like, what is sumlabel and grandtotal_label?)

Trusted Advisor
Posts: 1,018

Re: Sum of variable from 2 different proc statements with where condition for that variable

Posted in reply to NewbieToSAS

Take another look at Art's code, and you'll see you inserted a premature semilcolon in the proc print statement, and also a complete format statement.

 

Also, note that in data set v_need, you put all the "Yes" records prior to all the "No" records.  But down in the proc print, the "by doncolct;" statement tells sas to expect them in ascending order.  (i.e. "No", then "Yes').  You can address this either by reordering the records in V_NEED, or modify the by statement to

   by descending doncolct;

Solution
‎03-08-2017 11:20 PM
PROC Star
Posts: 7,468

Re: Sum of variable from 2 different proc statements with where condition for that variable

Posted in reply to NewbieToSAS

You also left one important semi-colon off. Try the following:

 

data v_need/view=v_need;
	set work.import (where=(DonColct='no'))
              work.import (where=(DonColct='yes')) ;
run;

proc print data=v_need;
	FORMAT donamt dollar11.2;
	
	sumlabel='#byval(DonColct) Total';
	grandtotal_label='Grand Total';
	
	TITLE1 'Earth Day Festival';
	TITLE3 'Sierra Club';
	TITLE4 'Apex High School Chapter';
	TITLE6 'Full Event Report';
	TITLE8 'Pledge Collected=yes';
	SUM donamt;
	
	VAR Sponsor donamt;
	by DonColct;
run;

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 9

Re: Sum of variable from 2 different proc statements with where condition for that variable

Thanks so much for the help (and bearing with me) guys! I really appreciated it :-)

 

mkeintz, your advice was really helpful as I had no idea how the "sumlabel" and "grandtotal_label" worked. In fact, while I was editing my code, the one I posted in my previous solution, I was suspicious about the fact that they lost their blue color, which they originally had when I copied them fro Art297's post. I also fixed the final code for sorting it in descending order, that one missed my eye.

 

Thanks for informing me about the semicolon, Art297! I was like banging my head over it for like 15 minutes Smiley Wink. Not to mention that the code you provided in the first place worked far better than my 2 proc statements combined.

 

I've posted the final code here, it works now (phew), and it gives the desired output (double phew). Thanks again guys :-)

 

data v_need/view=v_need;
	set work.import (where=(DonColct='yes'))
		work.import (where=(DonColct='no')) ;
run;

proc print data=v_need NOOBS SPLIT=' '
	sumlabel='DonColct'
	grandtotal_label='';;
	
	LABEL 
		donamt='Donation Amount Pledged'
		Sponsor=' Sponsor Name'
		DonColct='Pledge Collected';
	
	FORMAT donamt dollar11.2;
	
	TITLE1 'Earth Day Festival';
	TITLE3 'Sierra Club';
	TITLE4 'Apex High School Chapter';
	TITLE6 'Full Event Report';

	SUM donamt;
	
	VAR Sponsor donamt;
	by DESCENDING DonColct;
	PAGEBY DonColct;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 165 views
  • 5 likes
  • 3 in conversation