topic Re: How to split a variable string in proc report in SAS Programming

How to split a variable string in proc report

HitmonTran — Fri, 30 Apr 2021 22:08:37 GMT

I have a variable "col1" and I want to split the value into a new line in proc report when there's a "/" because there isn't enough space in the rtf.

Current output:

Current code:

data x; 
set y; 
col1= soc ||'/'|| pt; 
run; 

proc report data=final nowindows center headline headskip missing split='@'; 
column col:; 
define col1 / order style(column)={asis=on just=left cellwidth=.8in} flow style(header)={just=left font_size=9pt} "SOC/PT" ;
run;

Re: How to split a variable string in proc report

ChrisNZ — Sat, 01 May 2021 03:16:44 GMT

I have a variable "col1" and I want to split the value into a new line in proc report when there's a "/" because there isn't enough space in the rtf.

Current output:

Current code:
data x; 
set y; 
col1= soc ||'/'|| pt; 
run; 

proc report data=final nowindows center headline headskip missing split='@'; 
column col:; 
define col1 / order style(column)={asis=on just=left cellwidth=.8in} flow style(header)={just=left font_size=9pt} "SOC/PT" ;
run; 

Why split='@' and not split='/' if you want to split at / ?

Re: How to split a variable string in proc report

Ksharp — Sat, 01 May 2021 10:09:21 GMT

data x; 
set y; 
col1= soc ||' (*ESC*)n '|| pt; 
run;

Re: How to split a variable string in proc report

HitmonTran — Sat, 01 May 2021 13:15:56 GMT

Because I have columns with dates that uses "/". Either way, if i changed
"@" to "/" it doesn't work

Re: How to split a variable string in proc report

Tom — Sat, 01 May 2021 13:32:33 GMT

SPLIT works fine for plain old text listing output. But it looks like ODS ignores it.

data final; 
  soc ='AAAA';
  pt='BBBB';
  col1= soc ||'@'|| pt; 
run; 

proc report data=final nowindows center headline headskip missing split='@'; 
column col:; 
define col1 / order style(column)={asis=on just=left cellwidth=.8in} flow style(header)={just=left font_size=9pt} "SOC/PT" ;
run;

You will need to ODS compatible methods if you want to produce ODS output.

@Ksharp wrote:

data x; 
set y; 
col1= soc ||' (*ESC*)n '|| pt; 
run;

Re: How to split a variable string in proc report

Cynthia_sas — Sat, 01 May 2021 15:16:00 GMT

Hi:
SPLIT= is designed to allow control for splitting strings in column headers, not in data cells. Using the ODS ESCAPECHAR NEWLINE function is the best way to achieve what is needed. However, it will mean using a DATA step to alter the code so that \ turns into ESCAPECHAR + {newline 1} -- there is an older method using just ESCAPECHAR+n, but that is an artifact of the original ESCAPECHAR syntax and it might be better to switch to the newest syntax. Or you could use RTF control strings. Here's an example of all 3 methods.
Cynthia

The code for the above examples:

data fakedata; 
  length socpt $70;
  infile datalines dlm=',';
  input rownum socpt $;
  label socpt = 'Soc@PT'
        rownum = 'Row@Number';
return;
datalines;
10, Twas brillig and the slithy toves/did gyre and gimble in the wabe.
11, All mimsy were the borogroves/and the momeraths outgrabe.
;
run;

data newdata;
  length first last $70 newsoc soc2 soc3 $90;
  set fakedata;
  first = scan(socpt,1,'/');
  last = scan(socpt,2,'/');
  newsoc = catx('^{newline 1}',first,last);
  soc2 = catx('^n',first,last);
  soc3 = catx(' \line ',first,last);
run;

options orientation=landscape topmargin=.25in bottommargin=.25in 
        rightmargin=.25in leftmargin=.25in;
ods escapechar='^';
Title; Footnote;
  
ods rtf file='c:\temp\newline_datacell.rtf' startpage=no;
proc report data=newdata split='@';
  column rownum socpt newsoc;
  define rownum / order;
  define socpt / display 'Original@SocPT';
  define newsoc / display 'Soc PT@using new Escapechar';
  compute before _page_/style=Header;
    line 'Using New Escapechar';
  endcomp;
run;
    
proc report data=newdata split='@';
  column rownum socpt soc2;
  define rownum / order;
  define socpt / display 'Original@SocPT';
  define soc2 / display 'Soc PT@using old Escapechar';
  compute before _page_/style=Header;
    line 'Using Old Escapechar';
  endcomp;
run;
  
proc report data=newdata split='@';
  column rownum socpt soc3;
  define rownum / order;
  define socpt / display 'Original@SocPT';
  define soc3 / display 'Soc PT@using RTF control string'
         style(column)={protectspecialchars=off};
  compute before _page_/style=Header;
    line 'Using RTF Control Strings';
  endcomp;
run;
ods rtf close;

Re: How to split a variable string in proc report

Tom — Sat, 01 May 2021 15:43:54 GMT

@Cynthia_sas wrote:

Hi:
SPLIT= is designed to allow control for splitting strings in column headers, not in data cells.

...

Note that is not really a true statement since the SPLIT= option has always worked for splitting content for the listing output.

Perhaps you mean they only implemented the SPLIT= option for headers when they added ODS support?

Re: How to split a variable string in proc report

Cynthia_sas — Sat, 01 May 2021 20:23:25 GMT

Hi, Tom:

That was the way I remembered it too. But then I tested in 9.4M7 and found something different. Here's my LISTING output using / inside a datacell:

So I think you're right that it used to work that way in SAS 5 and 6 and even possibly after the formal ODS introduction in 8, but that's not what I could generate today.

Cynthia

Re: How to split a variable string in proc report

Tom — Sun, 02 May 2021 02:25:23 GMT

Did they change it in 9.4M7 ? Because the example I posted above is from 9.4M5 and it definitely split the value on the split character.

You have to use the FLOW option on the column definition in PROC REPORT.

Re: How to split a variable string in proc report

Cynthia_sas — Sun, 02 May 2021 18:50:27 GMT

Tom:

My bad. You're right. With the FLOW option, the LISTING destination honors the split character:

But FLOW is a LISTING-only option so, it is ignored for other ODS destinations like RTF, PDF and HTML. What I should have clarified in my original post was:

SPLIT= is designed to allow control for splitting strings in column headers, not in data cells for most ODS destinations like RTF, PDF and HTML. The SPLIT= option can be used in LISTING output for data cells (with the FLOW option in PROC REPORT), but ODS STYLE overrides are not used by the LISTING destination. If you want the data cells to be split in ODS destinations like RTF, PDF and HTML, then the workaround is to use ODS ESCAPECHAR or, for RTF, RTF control strings.

I find fewer students using LISTING output especially when working in SAS Studio with server-based SAS, so thanks for giving me the chance to refresh my LISTING skills.

Cynthia