topic Re: Sum of variable from 2 different proc statements with where condition for that variable in SAS Programming

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

NewbieToSAS — Thu, 09 Mar 2017 01:10:45 GMT

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?

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

art297 — Thu, 09 Mar 2017 01:24:04 GMT

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

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

NewbieToSAS — Thu, 09 Mar 2017 01:36:37 GMT

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:

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

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

mkeintz — Thu, 09 Mar 2017 01:39:09 GMT

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

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

NewbieToSAS — Thu, 09 Mar 2017 02:48:41 GMT

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?)

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

mkeintz — Thu, 09 Mar 2017 03:14:45 GMT

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;

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

art297 — Thu, 09 Mar 2017 03:25:23 GMT

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

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

NewbieToSAS — Thu, 09 Mar 2017 04:29:21 GMT

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 ;). 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;