BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NewbieToSAS
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

7 REPLIES 7
art297
Opal | Level 21

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

 

NewbieToSAS
Fluorite | Level 6

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

mkeintz
PROC Star

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

--------------------------
NewbieToSAS
Fluorite | Level 6

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

mkeintz
PROC Star

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;

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

--------------------------
art297
Opal | Level 21

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

 

NewbieToSAS
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 7 replies
  • 825 views
  • 5 likes
  • 3 in conversation