How do I use a enter as the separator for INTO :x SEPARATED BY '???'

[DougChar]

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.

[Tom]

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]

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]

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]

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;

[DougChar]

Thanks Tom

[ColinDK]

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]

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]

Well that's more compact - thx!

[lrcortland]

Thank you! This worked perfectly in SAS EG 9.4.