BookmarkSubscribeRSS Feed
DougChar
Calcite | Level 5

Hi,

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:

Report #3

Report #4

The following reports have run successfully:

Report #1

Report #2

Report #5

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.

9 REPLIES 9
Tom
Super User Tom
Super User

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

data _null_;

file 'mymail.txt';

put 'Hi there the results are:'

   / "&good"

;

run;

DougChar
Calcite | Level 5

Hi Tom,

I believe I'm writing it as a text file.

DATA _NULL_;

FILE myemail;

PUT "Please see attached for your daily report."

// &good;

RUN;

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"

And got

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.

Tom
Super User Tom
Super User

Try having the SQL query generate code that is valid in a PUT statement.


SELECT quote(trim(rpt_name))

  INTO :good SEPARATED BY ' / '

  FROM daily_rpt_status

  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" ;


Tom
Super User Tom
Super User

The other way to do it is to skip the SQL and just use the data set to drive the report.

data _null_;

  file myemail;

  if _n_=1 then do ;

    put "Please see attached for your daily report." ;

    if eof then put "No good reports.";

  end;

  if eof then put // "Generated by SAS (R) Software :)" ;

  set mydata end=eof;

  where rpt_status = 1;

  put @3 rpt_name ;

run;

ColinDK
Calcite | Level 5

Hi DougChar

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

SELECT rpt_name

INTO :good

SEPARATED BY "%sysfunc(byte(10))" 

FROM daily_rpt_status

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:

Tom
Super User Tom
Super User

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

ColinDK
Calcite | Level 5

Well that's more compact - thx!

lrcortland
Fluorite | Level 6
Thank you! This worked perfectly in SAS EG 9.4.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 7775 views
  • 7 likes
  • 4 in conversation