DATA Step, Macro, Functions and more

Adding a line feed character when joining two columns into one column

Reply
Super Contributor
Posts: 398

Adding a line feed character when joining two columns into one column

Hello all,
I would like to know if possible how to add a line feed character when joining two columns into one?

Col A
"THIS IS A TEST"

Col B
"JUST A TEST"

When joining them into Col C and displaying them I want them to appear as
"THIS IS A TEST"
"JUST A TEST"

Thank You for any help
SAS Super FREQ
Posts: 8,743

Re: Adding a line feed character when joining two columns into one column

Hi:
If you intend to use ODS HTML, ODS RTF or ODS PDF to display your output (such as in PROC PRINT or PROC REPORT), then you can use ODS ESCAPECHAR to insert a line feed or carriage return into a string. If you look in my SGF paper "Funny Stuff in My Code", there is an explanation of ODS ESCAPECHAR and using the line feed.
http://www2.sas.com/proceedings/forum2007/099-2007.pdf

With some procedures, you can force wrapping by adjusting cell width..which has the effect of looking like a line feed.

In the LISTING destination, there's not a good way to insert a line feed unless you use DATA _NULL_ and multiple PUT statements.

cynthia
Super Contributor
Posts: 398

Re: Adding a line feed character when joining two columns into one column

Cynthia,
Thank you for your reply. I'm not sure how the output will happen but there is no actual way to do it in sas in a data step other then using multiple PUT statements?
In the past in other languages I could use char(10) to put linefeeds within my string/char columns there isn't an equivalent in SAS?

Thank you again for your help
SAS Super FREQ
Posts: 8,743

Re: Adding a line feed character when joining two columns into one column

Hi:
Perhaps I'm dense or inexperienced, but having a line feed stored in the data doesn't make sense to me. As far as I know, there's no "universal" carriage return/line feed character that would work the same in operating systems like Unix, Windows, z-OS, Linux, or destinations like PDF, Word, HTML and Excel or applications like PowerPoint or PaintShop Pro or Adobe PageMaker.

So from a SAS standpoint, the ability to insert a line feed character into a text string or character variable depends on the destination that you are going to use to display the data -- because every different destination -might- use a different line feed character -- when you use a procedure or program that writes the data to the appropriate destination.

For example, RTF might use the \line RTF control string; HTML might use the <br> tag; some other destination might use '0D0A'x. The nice thing about ODS ESCAPECHAR is that, for RTF, PDF and HTML destinations, you can use an ODS ESCAPECHAR (such as '~' -- tilde) and then use ~n or ~2n to insert an escape character string which will be interpreted by the rendering viewer as a line feed. In SAS 9.2, the Escapechar syntax has a function-like way to insert a newline -- it is the ESCAPECHAR + the newline function: ~{newline}.

However, the LISTING destination is NOT one of the destinations that supports the insertion of LINE FEEDS, except in very controlled locations (such as with the split character) in PROC PRINT and PROC REPORT.

SAS is not WYSIWYG -- the data, as stored, is not the same as the displayed data. You could have a title on one report -- the data doesn't have a title or a footnote. In the data, dates are stored as just the number of days since Jan 1, 1960, but when you DISPLAY the date, you can display it on one report with mmddyy format and on another report with MONYY format....just to name a few of the differences.

For example, compare the LISTING output against the RTF, PDF and HTML output. You will see the ~n in the LISTING output, but the RTF, PDF and HTML will have line breaks in the "displayed" output.

cynthia

[pre]
data poem;
length pline $100;
infile datalines dsd;
input pline $;
return;
datalines;
"Twas brillig and the slithy toves~nDid gyre and gimble in the wabe,"
"All mimsy were the borogroves~nAnd the mome rathes outgrabe."
;
run;

ods listing;
ods html file='uself.html' style=sasweb;
ods rtf file='uself.rtf';
ods pdf file='uself.pdf';
ods escapechar='~';
proc print data=poem;
title 'Jabberwocky -- by Lewis Carroll';
run;
ods _all_ close;
[/pre]
Super Contributor
Posts: 398

Re: Adding a line feed character when joining two columns into one column

Cynthia,
Thanks for all your help but i'm still having problems.

I'm pretty much taking SAS Data and exporting it to Microsoft Access. What I need to do is have the string I create in SAS display correctly in Access.

I wanted to see if there was a carriage return or line feed character in SAS so when it exports to Access and I display the data will be displayed correctly with a carriage return or line break between the strings I concatenated.

Is there such a thing?
Respected Advisor
Posts: 3,777

Re: Adding a line feed character when joining two columns into one column

I can't test this No "ACCESS". But you can.

[pre]
data test;
A = "THIS IS A TEST";
B = "JUST A TEST";
c = catx('0a'x,a,b);
run;
proc export
data=test
outtable="test"
dbms=access
replace;
database=".\mydatabase.mdb";
run;

[/pre]
SAS Super FREQ
Posts: 8,743

Re: Adding a line feed character when joining two columns into one column

Hi:
As DATA _NULL_ has illustrated, SAS can insert -ANY- hex character into data. So now, to answer your question, you have to take SAS out of the picture and work this from the MS-ACCESS end.

You asked what you need to do to "have the string I create in SAS display correctly in ACCESS "? To me, the answer is that you have to figure out what the correct string is that ACCESS recognizes as a line feed (~LF~).

Here's what I'd recommend:
1) find out WHAT the hex string is in ACCESS -- if this was my task, I'd make a tiny ACCESS table, put the line feed into a data value and then EXPORT the ACCESS table in several formats -- TEXT, or CSV
2) Then, examine the data file that you created with ACCESS using a tool (such as NOTETAB or another tool) that will show you the file in hexadecimal format.
3) If you make your data a nonsense string like: ZZZZZZZZZ~LF~YYYYYYY, then it is fairly easy to find out what the hex values for Z and Y are. Whatever is left in the middle of the string -should- be the hex value that you need to use in your data in SAS that is the equivalent of the ~LF~ character.
4) This approach does assume that ACCESS does not tamper with the ~LF~ when it does the export to another format.

Alternately, you could look in the Microsoft documentation for ACCESS to see whether the keystroke or hex sequence for the linefeed character is documented.

cynthia
Super Contributor
Posts: 398

Re: Adding a line feed character when joining two columns into one column

Thank you both for your replies.

_null_ your solution looked like it was going to work but when I try and display it in a form it shows "THIS IS A TEST JUST A TEST" with a small box character between them. Your proc export statement helps me for a completely different issue. Thanks for the help.

Cynthia,
I did like you said and exported the data to a text file and then looked at it with a binary/hex viewer. It looks like 0D 0A is what is the hex value for carriage return and line feed. I'm sorry if you mentioned this but how within SAS can I add this to my string? I'm do apologize if I'm not grasping it correctly.

Thank you
SAS Super FREQ
Posts: 8,743

Re: Adding a line feed character when joining two columns into one column

Hi:
That funny character that you saw in ACCESS was how the '0A'x was interpreted by ACCESS. So that's good news. This statement
[pre]
c = catx('0a'x,a,b);
[/pre]

wrote out the '0A'x character. To get 2 hex characters, you would just need:
[pre]
c = catx('0d0a'x,a,b);
[/pre]

You can try this yourself by running this program:
[pre]
data _null_;
length a b $50 c $100;
a='Twas brillig';
b='and the slithy toves';
c = catx('0d0a'x,a,b);
file 'c:\temp\crlf.txt';
put @1 c;
run;
[/pre]


and then open the CRLF.TXT file with NOTEPAD -- you should see the text of A on one line followed by the text of B on another line.
This shows that the '0d0a'x is interpreted as a carriage return/line feed hex string.

Next, you would alter your data:
[pre]
data withcrlf;
length newvar $200;
set old;
newvar = catx('0d0a'x,var1,var2);
run;

[/pre]

Now, you would export the WORK.WITHCRLF file to ACCESS. If the '0d0a'x is not respected after the export to ACCESS,
you would have to work with Tech Support to find out whether the issue was at the SAS end of the export or the ACCESS end of taking in the data.

cynthia
Respected Advisor
Posts: 3,777

Re: Adding a line feed character when joining two columns into one column

The reason I choose '0A'x is because when in EXCEL that is the character that is entered when you "type" ENTER. I am usually more concerned with getting ride of them when IMPORTING from EXCEL.

I tested it with EXPORT using EXCEL and the following code. When you open the workbook in order for the '0A'x to be interpreted I needed to "auto fit columns" and then double click the cell. Then the cell displays with two lines. I imagine this would be the same in ACCESS. I expect this process could be automated.

[pre]
data test;
A = "THIS IS A TEST";
B = "JUST A TEST";
c = catx('0A'x,a,b);
do _n_ = 1 to 10;
output;
end;
run;
proc export data=test outfile='test0a.xls' replace;
run;
[/pre]
Super Contributor
Posts: 398

Re: Adding a line feed character when joining two columns into one column

_null_ & Cynthia,

Thank you so much for all your help. I got it using
catx('0d0a'x,a,b); I opened my access data in a form and the rows were split.

Thank you so much
PROC Star
Posts: 1,562

Re: Adding a line feed character when joining two columns into one column

Ah the beauty of standards! that was an entertaining thread!
Even Microsoft has varying standards for its EOL delimiter. Good stuff!

We'll never know why CP/M used both the 0D and 0A as if they used a teletypewriter.

Actually, just got me curious: it is all explained at http://en.wikipedia.org/wiki/Line_feed

ASCII was developed simultaneously by the ISO and the ASA, the predecessor organization to ANSI. During the period of 1963–1968, the ISO draft standards supported the use of either CR+LF or LF alone as a newline, while the ASA drafts supported only CR+LF.

Nothing like competing standards to enhance the life of users...

>Perhaps I'm dense or inexperienced

lol Cynthia! Smiley Happy

Jerry, as Cynthia explained, the reason EOL characters are normally not stored in sas is the same reason css was invented for html: to separate data from presentation, and the exceptions (as always, there are exceptions) are the split= option in some reporting procedures, and the ods escapechar.
Ask a Question
Discussion stats
  • 11 replies
  • 6482 views
  • 0 likes
  • 4 in conversation