How to output a newline character using PROC SQL to display in the Results Viewer?

[beacon]

Hi,

I'm using SAS 9.4 and when I run PROC SQL statements (without the NOPRINT option), the results will display in a new window in SAS that is named "Results Viewer". The column heading is in a slightly darker blue box with a border and bold, blue text. The values for the column are in a white box with a border and non-bold, black text. I refer to the white box as the "column values box".

If I have two sentences (or more) that I want to concatenate together with a newline character, then use it in a PROC SQL statement so it will display in the Results Viewer (with the assigned column name, then the column value with multiple lines), is that possible?

I have tried a number of things. The "dummyTable" in an actual dummy table in the database, so nothing is actually being pulled from the table (in case that wasn't clear).

1.

  Code:

%cr = %input('0D0A', $hex2.);
%let test = 'This is line #1.' || &cr. || 'This is line #2.';

proc sql;
    select
        &test. as test1
    from
        dummyTable
    ;
quit;

  Results:

  test1

  --------------------------------------------

  This is line #1. This is line #2.

2.

  Code:

%let test = %sysfunc(catx('0D0A'x, "This is line #1.", "This is line #2."));

proc sql;
    select
        &test. as test1
    from
        dummyTable
    ;
quit;

  Results (strange):

  This is line #2.                                 <--- Not sure why this is the column header instead of "test1".

  --------------------------------------------

  This is line #1.

3. 

  Code:

%let test = %sysfunc(cats("This is line #1.", %sysfunc(byte(13)), "This is line #2."));

proc sql;
    select
        &test. as test1
    from
        dummyTable
    ;
quit;

  Results:

  test1

  --------------------------------------------

  This is line #1. "This is line #2.         <--- Not sure why the double quote is appearing before the second 'This'.

My expected results are (the two sentences would be in the same "column value box" instead of having a separate "column value box" for each sentence):

  test1

  --------------------------------------------

  This is line#1.

  This is line #2.

For the project I'm working on, I cannot use a data set to store multiple lines to multiple rows because I'm limited to one row of output but the row of values can be multiple lines long. I've been storing the sentences in macro variables and trying to concatenate them, but I used the sentences in the examples above to simplify my question.

Thanks!

[Tom]

Get the SAS syntax right before playing with macro variables.

Also use a test that actually make sense.  This shows how to the concatenation operator in a SELECT statement to build a value from multiple pieces.

proc sql;
    select
        'This is line #1.' || '<line break>' || 'This is line #2.' as test1
    from
        sashelp.class(obs=1)
    ;
quit;

If you use 

'This is line #1.' || '0D0A'x || 'This is line #2.'

instead then the actual CR and LF will be there. 

But writing it to the screen is not a good test.  Store it into a dataset and check that it is there.

proc sql;
create table test1 as
 select
        'AA' || '0D0A'x || 'BB' as test1
    from
        sashelp.class(obs=1)
 ;
quit;

23   data _null_;
24    set test1;
25    put test1= / test1= $hex. ;
26   run;

test1=AA
BB
test1=41410D0A4242
NOTE: There were 1 observations read from the data set WORK.TEST1.

[Reeza]

Your new line character depends on your destination - what output type are you using, listing, HTML, Excel, PDF? And what works in one may not work in another. Any reason to not use proc report to display the data and control the column width to have the lines of text wrap as needed?

[ballardw]

You are not quite understanding what the macro processor returns.

The code you would generate looks like. The macro variable test would resolve to something like:

For your code 2 example:

You really should use a %put to see what the macro variable looks like as that is how the compiler sees the result such as:

%let test = %sysfunc(catx('0D0A'x, "This is line #1.", "This is line #2."));

%put &test;

So the Proc SQL actually looks like

proc sql;
    select
        "This is line #1."'0D0A'x"This is line #2." as test1
    from
        junk
    ;
quit;

and the 0D0A gets "eaten" by the compiler as a line in the CODE, not the values.

So you can see that you are assigning "This is line#2" as the value to Test.

The macro processor generates code and need to pay close attention to order.

You may actually want to look at for comparison

%let test = catx('0D0A'x, "This is line #1.", "This is line #2.");

proc sql;
    select
        &test. as test1
    from
        dummytable
    ;
quit;

to address line 2 over line 1 issue.

In many places on approach to allowing quotes inside of text values is to use a doubled character "This string contains a "" in the body of the line" will resolve to: This string contains a " in the body of the line

This is a basic syntax feature in SAS. So the

ODS destination creation does things with your values and inserting specific character codes does not always translate through the ODS destination as intended. The result window is HTML output and would require inserting HTML tags . SAS provides some functions with the ESCAPECHAR that allow inserting the appropriate tags for some ODS destinations.

Perhaps this does what you want:

ods escapechar='^';

proc sql;
    select
        "This is line #1.^{newline}This is line #2." as test1
    from
        dummytable
    ;
quit;

All that appearance information you provided comes from your current ODS style, likely HTMLBlue. You change that by specifying a different default style for the session or providing a STYLE option on an ODS destination