12-29-2016 10:42 AM
I'm new to SAS programming, and I am struggling with how to take the table I created and generate a text output with a very specific format. The report is used for tax purposes at my company, and the text output must follow a certain order and meet the proper space requirements in order for the output to read into the tax system correctly. In addition, the pattern/order that these records must flow in differ between federal taxes and state taxes. If anyone has experience similar to this, I would greatly appreciate the help...I've been stuck on this for over two weeks!
I attached the query that I have thus far to provide some context. I am writing this in SAS Enterprise Guide 6.1
12-29-2016 12:11 PM
Are you asking for help with your very long program? Or are you asking for help with the ODS RTF step at the end of the program?
Typically, your ODS RTF step should look something like this (you should be able to run this code. All you might have to do is change the path name inside the FILE= option -- and don't forget the file extension of .RTF after the file name:
ODS RTF file='c:\temp\myfile.rtf' bodytitle style=journal;
*** SAS code here to generate output goes next;
title font='Times New Roman' h=12pt "My Title";
footnote font='Times New Roman' h=9pt "My Footnote";
proc print data=sashelp.class;
ODS RTF CLOSE;
Note that the ODS RTF statements are like a "sandwich" where the 2 pieces of bread (the ODS RTF statements) surround the step(s) in the middle.
What I note about your code is that either the program ends abruptly or you neglected to provide an ODS RTF CLOSE at the end. But, since you're using Enterprise Guide, that is not as troublesome as the fact that you have a TITLE and a FOOTNOTE statement, but nothing that produces output, your stray SET, RETAIN and KEEP statements will not produce any output. So, I suspect that when you run your program, you get either nothing or an empty RTF file. (And, I suspect, you might not even be able to open the file, since you did not use a .RTF file extension in the code:
ODS RTF FILE = '\\Client\C$\Users\n0294503\Documents\Empower' bodytitle startpage = no style = journal; options papersize = A4 orientation = portrait; title1 /*bold*/ /*italic*/ font = 'Times New Roman' height = 12pt justify = center underlin = 0 color = black bcolor = white 'Table 1 name'; footnote1 /*bold*/ /*italic*/ font = 'Times New Roman' height = 9pt justify = center underlin = 0 color = black bcolor = white 'Note: Created on January 2012'; /* ignore this for now retain TYPE TYPESIMP FEIN Amount PayrollID Wage UnitID PAYDATE Tax TranslationCode; set Master; keep TYPE TYPESIMP FEIN Amount PayrollID Wage UnitID PAYDATE Tax TranslationCode; run; */
You say in your note that you need to create "text output" and the output must follow a certain order and meet proper space requirements, but you do not show what the output looks like. Typically, I do not think of RTF output as being "text" output, since an RTF file is meant to be opened with Microsoft Word and is meant to be rendered in a proportional spaced font.
When you say "proper space requirements", you could mean a 1 inch top, bottom left and right margin or you could mean a completely free format ASCII text file with name starting in column 1 of the output file or some other requirements. Without a sample of what you're trying to produce, it is hard to comment on those requirements. There are several ways to create output using ODS RTF or a text file as the final result. The screen shot below shows these 3 ways:
1) Using PROC PRINT with an ODS RTF "sandwich"
2) Using DATA _NULL_ with an ODS RTF "sandwich" and using DATA _NULL_, FILE PRINT and PUT statements
3) Using DATA _NULL_ withOUT any ODS RTF and writing directly to an ASCII text file and shown in Notepad
There is a more advanced way to write output using the new Report Writing Interface, which was just introduced as production in SAS 9.4. However, if you are still running Enterprise Guide 6.1, then my guess is that you are still running SAS 9.2 or 9.3.
First, I think your task is to experiment a bit with ODS RTF and figure out whether you need tabular output or "free-form" output. Then, you need to know whether you can use ODS RTF and PROC PRINT and PROC REPORT to get what you want or whether you need to use a DATA _NULL_ step. Finally, you will have to put something at the bottom of your long program that produces the type of output you want.
It looks to me in this step, at the bottom of the program:
proc sort data=IMSHR.Master_V2 OUT=IMSHR.Master_v3 (keep=typesimp fein amount payrollid wage unitid paydate tax translationcode); BY product StateCd; run; /*SORTING THE DATA IN THE ORDER THAT IT APPEAR IN THE REPORT*/ Data IMSHR.MasterOrdered; Retain typesimp fein amount payrollid wage unitid paydate tax translationcode; Set IMSHR.Master_V3; Run;
That your IMSHR.MasterOrdered dataset has the variables you want. Have you tried something like this just to see what you get?
options papersize = A4 orientation = portrait date number; ODS RTF FILE = '\\Client\C$\Users\n0294503\Documents\Empower.rtf' bodytitle startpage = no style = journal; title1 font='Times New Roman' h=12pt 'Table 1 name'; footnote1 font='Times New Roman' h=9pt 'Note: Created on January 2012'; proc print Data=IMSHR.MasterOrdered noobs; var typesimp fein amount payrollid wage unitid paydate tax translationcode; run; ods rtf close;
It might get you started producing something. Since you did not post what your desired output was supposed to look like, it is hard to make recommendations or suggestions.
12-29-2016 12:27 PM
Hi Cynthia -
Thank you for your prompt feedback. I am sorry that I neglected to attach a sample of the output (please see above).
I am not asking for help with the program (that's in a good place) just the ODS RTF step at the end...which I know is incomplete, I am still learning and don't know what parts of the step to omit or modify based on the report requirements.
Since the format of the report does need to be in a text format (traditionally, the output is opened in Notepad), should I be using something other than RTF?
In terms of proper space requirements, I am not referring to margins. Rather, each field in a given record needs to start at a specific position, and there needs to be a certain number of blanks between fields, given the type of record. The reason for this is because once the text output is produced, that output is sent to our accounting team who takes that text file and uploads it to a system called Empower (this will only read the data properly if it follows the required format).
I also tried to run the code that you suggested - the code ran but I am getting an error regarding administrative access and the ability to send the output to the desired path. Working with our SAS/IT team to get that resolved (classic corporate problems!).
Thank you again!
12-29-2016 03:38 PM - edited 12-29-2016 03:39 PM
If you need to produce an ASCII text file in the format you show, then you should look at some variation of Report #3 from my previous mail. That involves the use of DATA _NULL_ with PUT statements. The very "left" side of the file is conceptually position @1 and the next position is @2, etc.
You should NOT be using ODS RTF if the file should be opened in Notepad and/or passed onto another system.
In fact, this free tutorial will get you started:
http://support.sas.com/training/tutorial/el/libsppg2.html look at the video entitled "Creating Raw Data Files Using the DATA Step " for a start.
Once you get the write permissions figured out and assuming that you know the layout (the exact columns positions) that each different row needs to have, then you should be able to write a program, such as the one that produced this output.
Here's the full program.
ods _all_ close; data _null_; set sashelp.class(where=(age eq 12)) end=eof; file 'c:\temp\data_null_examp.txt' ; if _n_ = 1 then do; put 'This is the ruler, so you can understand @position'; put '.........1....1....2....2....3....3....4....4....5....5....6....6....7'; put '1234567890....5....0....5....0....5....0....5....0....5....0....5....0'; end; if sex='M' then do; put @1 'H' @4 sex @5 name @25 age 2.0 @30 height 5.1 @45 weight 5.1; end; else if sex = 'F' then do; put @1 'W' @2 name @10 sex @40 age 2.0 @50 height 5.1 @60 weight 5.1; end; if eof=1 then do; put ' '; put '1234567890....5....0....5....0....5....0....5....0....5....0....5....0'; put 'This is the end of the file'; end; run;