BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9

Good afternoon,

 

A few questions - I'm using base SAS 9.4

 

1)  If I'm creating an output dataset using proc report (output out=),

do I need to use a sandwich since I'm not using HMTL or outputting to ?

 

 

2)  I'm using the following code but getting unexpected results

  a)  the numbers in pctvar5 aren't displaying although you can see the cell has a value if you click on it

  b) the formats I've assigned aren't showing in the excel output (the output is rounded to a whole percentage instead of having 2 decimals)

 

 

3) How do I get column H to display and how do I get excel to display the output?  I tried using ods tagsetsxp and everything displayed as expected but it gives percentage signs for each value

 

Thank you!

 

p.s. I tried attaching a screenshot but I got an error message saying the file extension wasn't valid. I wasn't able to delete the file so I just uploaded a test blank program

 

 

title; footnote;

ods _all_ close;

 

 

ods excel file= file="c:\want.xls" style=journal;

ods excel options(sheet_name='Table 1' autofit_height='yes' width_fudge='0.58');

proc report data=have nowindows headline headskip ;

column county ('YEAR' year)('TOTAL NUMBER' total) ('CHILDREN WITH VAR1 pctvar1)

(CHILDREN WITH VAR2 pctvar2) ('CHILDREN WITH VAR3' pctvar3) ('CHILDREN WITH VAR4' pctvar4)

(CHILDREN WITH VAR5' pctvar5) ('CHILDREN WITH VAR6' pctvar6);

define county / group order;

define year / group order=internal descending ' ';

define total / analysis 'NUMBER' format=comma8.;

define pctvar1 / display right 'PERCENT' format=percent7.2 ;

define pctvar2/ display 'PERCENT' format=percent7.2 ;

define pctvar3 / display 'PERCENT' format=percent7.2 ;

define pctvar4 / display 'PERCENT' format=percent7.2;

define pctvar5 / display 'PERCENT' format=percent7.2;

define pctvar6 / display 'PERCENT' format=percent7.2 ;

compute after county; line ' '; endcomp;

run;

 

 

ods excel close;

 

 

 

 

 

6 REPLIES 6
SASKiwi
PROC Star

I usually find that you have to add a TAGATTR style attribute to percentages to get them to display correctly in Excel:

 

define pctvar1 / display right 'PERCENT' format=percent7.2 style=[tagattr="format:#,##0.00%"];

 

Also what is your SAS 9.4 maintenance level? ODS Excel was only added in 9.4M3 and probably works better in M4 or M5. 

jcis7
Pyrite | Level 9

M2. What if I use EG?  Thank you!

SASKiwi
PROC Star

ODS Excel is not Production in M2. I suggest you try the ExcelXP tagset instead. We are on M2 as well and stick with ExcelXP. EG is irrelevant with this issue. You would get the same problem regardless of which SAS interface you use.

jcis7
Pyrite | Level 9
Thank you!




ballardw
Super User

@jcis7 wrote:

Good afternoon,

 

A few questions - I'm using base SAS 9.4

 

1)  If I'm creating an output dataset using proc report (output out=),

do I need to use a sandwich since I'm not using HMTL or outputting to ?

 

 


The procedures that have an out= to create data sets do not need any ods destination set though you may be a tad surprised about the layout/content of the file depending on what procedure and options you use. If you use ODS OUTPUT statements then you have to have at least one ods destination open though a "sandwich" isn't needed for the single procedure.

3) ...  I tried using ods tagsetsxp and everything displayed as expected but it gives percentage signs for each value

You use the format PERCENT7.2. That format displays values with a percent sign.

 

 

jcis7
Pyrite | Level 9

Thank you!

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 3628 views
  • 2 likes
  • 3 in conversation