topic Re: Using Proc Report and saving to a dataset in SAS Procedures

Using Proc Report and saving to a dataset

jcis7 — Fri, 20 Jul 2018 23:31:16 GMT

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;

Re: Using Proc Report and saving to a dataset

SASKiwi — Sat, 21 Jul 2018 06:25:31 GMT

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.

Re: Using Proc Report and saving to a dataset

ballardw — Mon, 23 Jul 2018 17:15:38 GMT

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

Re: Using Proc Report and saving to a dataset

jcis7 — Tue, 24 Jul 2018 19:44:36 GMT

Thank you!

Re: Using Proc Report and saving to a dataset

jcis7 — Tue, 24 Jul 2018 19:50:01 GMT

M2. What if I use EG? Thank you!

Re: Using Proc Report and saving to a dataset

SASKiwi — Wed, 25 Jul 2018 04:00:58 GMT

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.

Re: Using Proc Report and saving to a dataset

jcis7 — Wed, 25 Jul 2018 17:23:42 GMT

Thank you!