The SAS Output Delivery System and reporting techniques

Proc Report output to text

Reply
Frequent Contributor
Posts: 97

Proc Report output to text

Can we generate a txt file through Proc Report

Currently I am using the below mentioned code to generate a txt output

OPTIONS NODATE NUMBER LS=250 PS=100;
TITLE;FOOTNOTE;
ODS LISTING;

FILENAME outfile "/apps/ReportsFolder/MyReport.txt;

PROC PRINTTO FILE=outfile NEW;
RUN;

I am usng List data to show the output.
I am not able to customise the group total labels
SAS Super FREQ
Posts: 8,864

Re: Proc Report output to text

Hi:
I have some observations:

1) This syntax works:
[pre] ods listing file='yadayada.txt';[/pre]
as an alternative to PROC PRINTTO. But I question why you're even using PROC PRINTTO or creating a TXT file in the first place. (You can create a TXT file from inside EG by going to Tools --> Options and not using PROC PRINTTO -- unless you explicitly WANT to create a TXT File. But then, see #2)

2) What kind of output are you routing to the .TXT file??? Is it output from a reporting procedure like PROC PRINT, PROC REPORT, PROC TABULATE or DATA _NULL_? Or, is it output from some other procedure like PROC GLM or PROC REG, PROC MEANS, PROC FREQ? Why do you need this output in a .TXT file?

3) And then, ????? this is where I'm not sure what's happening???? You want to do -something- with the file that was created with PROC PRINTTO???

4) In which step do you want to customise the group total labels??? When you first create the .TXT file or when you open the .TXT file after it's been created?

Perhaps your best bet for help with this is to contact Tech Support.

Scenario 1:
If you want to customize the group total labels from the List Data task, that is hard to do. You can only customize the "N" or the label for the count using PROC PRINT (which is behind the scenes of the LIST data task). If the group total that you want to change is the N, then Tech Support can show you how to do that.

Scenario 2:
If the group total that you want to change is the total at the break, (like between Regions or at the bottom of the report), then you cannot change what you get from the LIST Data task. But, you CAN switch to PROC REPORT in order to get something like this:
[pre]
Region Product Inventory
Asia Boot $170,165
Men's Casual $2,176
Men's Dress $272,634
Sandal $36,570
Slipper $485,082
Sport Shoe $16,057
Women's Casual $52,827
Women's Dress $140,628
Asia SubTotal $1,176,139

Canada Boot $1,362,772
Men's Casual $909,052
Men's Dress $2,327,082
Sandal $99,035
Slipper $3,459,849
Sport Shoe $765,695
Women's Casual $895,434
Women's Dress $3,291,790
Canada SubTotal $13,110,709

Grand Total $14,286,848
[/pre]

(Where the "Asia SubTotal", "Canada SubTotal" and "Grand Total" are all customized text that I was able to write with PROC REPORT).

Tech Support can help you with the syntax for either scenario. I'm just confused by why you need the PROC PRINTTO step in the first place.

cynthia
N/A
Posts: 0

Re: Proc Report output to text

Posted in reply to Cynthia_sas
Can you please send me the code for the scenario#2 where you have used PROC Report..
SAS Super FREQ
Posts: 8,864

Re: Proc Report output to text

Posted in reply to deleted_user
Hi:
Here's the code that I used to generate the output (above) for Scenario 2:
[pre]
options nocenter nodate nonumber;
ods listing;
ods html file='c:\temp\report.html' style=egdefault;
proc report data=sashelp.shoes nowd;
where region in ('Asia', 'Canada');
column region product sales inventory;
define region / group;
define product / group;
define sales/ noprint;
define inventory/ 'Inventory';
break after region /summarize skip;
rbreak after / summarize;
compute after region;
region = trim(region)|| ' SubTotal';
endcomp;
compute after;
region = 'Grand Total';
endcomp;
run;
ods html close;
[/pre]

This technique will only work in the way I show if
1) Region is "wide" enough to hold the whole string. In sashelp.shoes, Region is a length of $25 -- so there is enough room for these two Regions. But if I take off the WHERE clause, there is a Region called "Central America/Caribbean" which is a very long Region value and the string: Central America/Caribbean SubTotal is 34 characters long, so it won't "fit" in the available space. I'd have to do something in my compute block to fix this region value:
[pre]
compute after region;
if region = 'Central America/Caribbean' then region = 'Cent Amer/Carib';
region = trim(region)|| ' SubTotal';
endcomp;
[/pre]
2) or, if region was a "code" like 'AS' or 'PA' or 'CN' (with a length of $2), then I'd have to use a user-defined format or some other technique to put a custom text string at the break.
and
3) The variable that I'm renaming needs to be character variable. If Region was a numeric code (like 1, 2, 3), then I would have to use other techniques to customize the break lines.

Tech Support could help you if your data falls into either #2 or #3 above.

cynthia
Frequent Contributor
Posts: 97

Re: Proc Report output to text

Posted in reply to Cynthia_sas
Hi Cynthia

The code and output you specified is acctually what I wanted. We can do a lot of stuff through PROC REPORT I think. But all our reports are supposed to be text files. These text files should be saved for some reason.

I am using Enterprise Guide to create the report and then creating a Stored Procedure out of it. The tasks I use at the end is a List data task with text as results. List data uses Proc Print.

At the beginning of the project I add a code node having the following code. This project on executing create a text output

=========================
OPTIONS NODATE NUMBER LS=250 PS=100;
TITLE;FOOTNOTE;
ODS LISTING;

FILENAME outfile "/apps/sas/Reports/MyReportFile.txt";

PROC PRINTTO FILE=outfile NEW;
RUN;
=========================

Now using List data task can give me the outputs as mentioned in the following url
http://support.sas.com/ctx/samples/index.jsp?sid=973&tab=output

Hence I wanted to know if I could get similar output as that of PROC REPORT using List data. i.e
- Customised Sub total labels and grand total labels.
- Report Title and Report Footer
- Page header and Page footer
- Page number and total number of pages
- Using format on particular columns to wrap the text.

Regards
Piece of Grass
Frequent Contributor
Posts: 97

Re: Proc Report output to text

When I use PROC PRINT and grouping I get groups with a hyphened lines.

---------------------------- Code=1130 Order Category=C ------------------------------------

I want to avoid these lines and just want the output as shown

Code=1130 Order Category=C


Code used is a proc print

PROC PRINT DATA=WORK.SORTTempTableSorted
NOOBS
WIDTH=MINIMUM;
VAR Department Cost Sales Profit
SUM Cost Sales Profit;
RUN;


Also,
the results shown in url http://support.sas.com/ctx/samples/index.jsp?sid=973&tab=output
does not have ----- lines in the group line
SAS Super FREQ
Posts: 8,864

Re: Proc Report output to text

Not sure exactly what's going on with the Tech Support sample. When you talk to the folks in Tech Support, you might ask them what options were in effect when they created the output. It looks to me like they had the NOCENTER option turned on, which suppresses the dashed line. The NOBYLINE option can also turn off the BYLINE. Also, the ID statement has a side effect of turning off the BYLINE.

cynthia
SAS Super FREQ
Posts: 8,864

Re: Proc Report output to text

Hi:
Your questions:
"Hence I wanted to know if I could get similar output as that of PROC REPORT using List data."

Remember that PROC REPORT just does not have a task. What you are calling the List Data task is really just PROC PRINT with a nice front end to save you from having to learn PROC PRINT code. And the general answer is that PROC PRINT is the simpler of the 2 procedures -- you can do a LOT with PROC PRINT -- but you can do MORE with PROC REPORT. Most of the things you want to do (customized totals/ subtotals, etc) are reserved for PROC REPORT and are not available in PROC PRINT/LIST Data task.

- Customised Sub total labels and grand total labels.
Only with PROC REPORT, not with LIST Data task (will work in all destinations)
(see Example 2 code or code shown above)


- Report Title and Report Footer
Yes with PROC REPORT, not with LIST Data task. If by report title & report footer you some text that is within the boundary of the table.
Yes, with the LIST Data task, if you mean any text that you could place before or after the procedure with ODS TEXT=. (COMPUTE block and LINE will work in all destinations with BASE SAS; ODS TEXT= does not work for LISTING; text written with a LINE statement will not be surfaced in Web Report Studio via a stored process) (see Example 2 code)


- Page header and Page footer
Yes because the SAS title statement goes at the top of every page and at the bottom of every page....no matter what your procedure is. However, different destinations might treat the SAS title and SAS footnote differently -- for example, an HTML page will only have the SAS title at the top of the table and the SAS footnote at the bottom of the table; but PDF and RTF will show the SAS title on EVERY page and the SAS footnote on EVERY PAGE. (see either Example and Title/Footnote statement treatment)

- Page number and total number of pages
Yes, for destinations that support Page X of Y -- RTF and PDF.
Sort of, for LISTING but it takes a LOT of work and post processing of the file.
No, for HTML because HTML is a "pageless" destination -- remember that HTML was going to lead us to the paperless office. I like to joke that there are no page numbers in the paperless office. See Example 2 code.


- Using format on particular columns to wrap the text.
Yes, but not with a format, for destinations that support style overrides -- you can change the cellwidth, that will force text to wrap. This means yes for RTF, PDF and HTML. But it means no, for the LISTING destination, because the LISTING destination (txt file) does not support style=overrides. If you are using the List Data task and sending your output to LISTING, then there is no way to force wrapping. You can use the FLOW and WIDTH options with PROC REPORT -- but this only works for LISTING. See Example 1 code.

For more help with this, I think you really need to work with Tech Support. You will find that when creating TXT files, some techniques (LISTING destination techniques) will work. Then, when you go to use those techniques within a stored process, they will NOT work the way they work in LISTING -- this is because every time you execute a stored process (assume this is what you mean when you say "stored procedure"), the results might be coming back to the client applications in a non-TXT format -- for example -- SP results come back to PowerPoint as SASReport XML; SP results come back to EG as HTML, but you can change the result type; SP results come back to Excel as SASReport XML -- but you can change the result type; SP results come back to Web Report Studio as SASReport XML -- you CANNOT change this result type.

To do a lot of what you want to do, you may have to switch to PROC REPORT. Some things, like Page X of Y page numbering really will NOT work for LISTING, but will ONLY work for RTF or PDF -- that means they won't work for HTML. You may find that you need one version of the code for the TXT file and another version of the code for HTML and another version of the code for RTF and PDF. In any event, Tech Support can help you sort out which of these techniques is the right choice for what you need to do.

(I'm not showing results, since you can run the code -- the code itself is long enough.)

cynthia

Example 1 code:
CELLWIDTH vs FLOW vs FORMAT
[pre]
title; footnote;
options linesize=180;
data testit;
set sashelp.class;
charvar = 'Twas brillig and the slithy toves did gyre and gimble in the wabe.';
charvar2 = 'All mimsy were the borogroves and the mome raths outgrabe.';
charvar3='Beware the Jabberwock, my son!~nThe jaws that bite, the claws that snatch.';
output;
run;

ods listing;
ods html file='c:\temp\wrap.html' style=egdefault;
ods rtf file='c:\temp\wrap.rtf';
ods pdf file='c:\temp\wrap.pdf';
ods escapechar='~';
** note that charvar3 has an explicit line feed for RTF, PDF and HTML: ~n;

proc print data=testit;
title 'Proc Print';
title2 ' LISTING will not use cellwidth; format of $20 will truncate value';
where age gt 14;
var charvar / style(data)={cellwidth=1in};
var charvar2 charvar3 name;
format charvar2 $20.;
run;

proc report data=testit nowd;
where age gt 14;
title 'Proc Report';
title2 'Listing will use FLOW, other Destinations will use cellwidth';
column charvar charvar2 charvar3 name;
define charvar / style(column)={cellwidth=1in};
define charvar2/ flow width=20;
define charvar3 / display;
define name/ display;
run;
ods _all_ close;
[/pre]

Example 2:
PAGE HEADERS, Extra Text, Page Numbering, Report Headers, etc
[pre]

options center nodate nonumber;
ods escapechar='~';

ods listing;
ods html file='c:\temp\report.html' style=egdefault;
ods rtf file='c:\temp\report.rtf' ;
ods pdf file='c:\temp\report.pdf' startpage=no;

** there is no TEXT= statement allowed for LISTING destination files;
ods rtf text="Twas brillig and the slithy toves";
ods pdf text="Twas brillig and the slithy toves";
ods html text="Twas brillig and the slithy toves";

proc report data=sashelp.shoes nowd;
title 'At Top of Page ~{thispage} of ~{lastpage}';
footnote 'At Bottom of Page';
where region in ('Asia', 'Canada', 'Pacific', 'Western Europe');
column region product sales inventory;
define region / group;
define product / group;
define sales/ noprint;
define inventory/ 'Inventory';
break after region /summarize skip;
rbreak after / summarize;
compute before _page_;
line "Before Report Table";
endcomp;
compute before region;
line "Before Region";
endcomp;
compute after region;
if region = 'Central America/Caribbean' then region = 'Cent Amer/Carib';
region = trim(region)|| ' SubTotal';
line "After Region";
endcomp;
compute after;
region = 'Grand Total';
line "After Report Table";
endcomp;
run;
ods _all_ close;
[/pre]
Ask a Question
Discussion stats
  • 7 replies
  • 796 views
  • 0 likes
  • 3 in conversation