11-14-2013 06:49 PM
I'm trying to create the foundation for an email which would summarize for me which reports that I've scheduled had a successful output and which ones did not. I'm basing this all off an excel list which is uploaded daily and is my base reference for which reports should have run. At the end of each report code, I write to a table which tells me that the report had a successful output (if there is no record in the table for the report, then its assumed that the report did not output successfully). My end goal is for the body of the final email to look something like this:
The following reports were unsuccessful:
The following reports have run successfully:
My dataset for this effort looks like this:
rpt_num rpt_name rpt_status
1 Report #1 1
2 Report #2 .
I have been trying to do this via PROC SQL, but am stuck and not able to get the INTO statement SEPARATED BY to use a carriage return.
PROC SQL NOPRINT;
SELECT rpt_name INTO :good SEPARATED BY '0D'x' FROM daily_rpt_status WHERE rpt_status= 1;
PROC SQL NOPRINT;
SELECT rpt_name INTO :bad SEPARATED BY '0D'x' FROM daily_rpt_status WHERE rpt_status IS MISSING;
Then I would use the &good and &bad to fill in the email body.
Any thoughts on this would be greatly appreciated. Also, I'm not tied to doing things this way, especially if there is a more efficient way to do it. This is just based off what I know today.
11-14-2013 07:09 PM
How are you generating the body of the email message? If you are writing to a raw text file then use the end of line characters for your operating system. LF for Unix ('0A'x) and CR+LF for Windows ('0D0A'x).
put 'Hi there the results are:'
11-14-2013 07:31 PM
I believe I'm writing it as a text file.
PUT "Please see attached for your daily report."
I did try using the ('OA'x) as my separater, but I get a syntax error.
SEPARATED BY '0A'x' OR SEPARATED BY '(0A'x)'
I also tried
SEPARATED BY "0A'x"
REPORT 10A'xREPORT 2
I know that it must have something to do with the single quote in the middle of the 0A'x, but I just can't seem to figure out the right combination of single/double quotes to get it right.
11-14-2013 07:41 PM
Try having the SQL query generate code that is valid in a PUT statement.
INTO :good SEPARATED BY ' / '
WHERE rpt_status= 1
Then this line in a data step :
put &good ;
Will be the same as this hard coded line.
put "Report #1" / "Report #2" / "Report #5" ;
11-14-2013 07:52 PM
The other way to do it is to skip the SQL and just use the data set to drive the report.
if _n_=1 then do ;
put "Please see attached for your daily report." ;
if eof then put "No good reports.";
if eof then put // "Generated by SAS (R) Software " ;
set mydata end=eof;
where rpt_status = 1;
put @3 rpt_name ;
07-07-2014 10:01 AM
So I reckon this is a pretty stale thread by now, but I just found a way around what I think you were looking for (and I certainly was):
So we know that the separator has to be a simple character string - so let's create a line feed character using BYTE(10). But we need something in quotes, so just stick the function into a %SYSFUNC() structure like this
SEPARATED BY "%sysfunc(byte(10))"
WHERE rpt_status= 1;
Obviously this is just Line Feed = BYTE(10) for *nix systems - for Windows you need Carriage Return = BYTE(13) as well - "%sysfunc(byte(13))%sysfunc(byte(10))" .
Better late than never! :smileygrin:
07-07-2014 10:09 AM
You can use hex literals for codes in SAS, but the original poster had the syntax mangled.
To just use a linefeed as the delimiter use:
separated by '0A'x
To use both a CR and LF then use
separated by '0D0A'x