Hi:
Here's what I understand.
1) you have original data that is multiple observations per "ID"
2) the observations fall into multiple "types" for each ID. The "types" you have shown us are "Class" and "Teacher"
3) each observation has column/field called TXT, which is an explanatory text string
Here's what I don't understand. You keep referring to an output
DATASET as though it was possible for a SAS
DATASET to contain a carriage control or line feed. A SAS
DATASET is a proprietary structure of rows and columns, and as such, does not contain line feeds or carriage returns, such as you implied in your first post, when you said:
"Then my output shd be like this, where 'abc' 'def' are in the same record but different lines, i need kind of a line break."
[pre]
1 abc
def
2 efg
ghi
[/pre]
If SAS were going to store your data into a SAS DATASET structure, it would either be something like this where each value of TXT for ID would become a numbered variable:
[pre]
ID TXT1 TXT2
1 abc def
2 efg ghi
[/pre]
OR like this, where your program "concatenated" all the individual values for TXT together into one long variable, possibly with a delimiter of some kind between each individual value for TXT (shown below as the | character):
[/pre]
ID AllText
1 abc | def
2 efg | ghi
[/pre]
There is no "kind of line feed" in a SAS DATASET. If, on the other hand, you created a REPORT (such as with PROC REPORT), then you could line up 'def' underneath 'abc' for one value of ID. Or, you could write a custom DATA step program that would structure your output REPORT that way.
But then, in another post, you said:
"Sorry I forgot to mention that after vertical concatenation, SAS dataset will be uploaded back to a teradata table. So I guess I need some basic formatting that will be supported by a table."
Ah, OK...the SAS dataset will be uploaded back to a Teradata table -- so either you are going to use SAS/Access for Teradata in order to move SAS data into Teradata form or you are going to use????
When you said "I guess I need some basic formatting that will be supported by a (Teradata) table" -- my question to you is -- do you know what kind of formatting that would be???
On this web site:
http://teradatau.courses.teradata.com/learning/BLADE_MS/legacy/35502_IntrotoTD_DEMO/wbt-glossary.htm
it says that:
"Each Teradata Database table is stored in a set of subtables. There is one table for each kind of data, including:
----Table headers
----Primary data
----Fallback data
----Secondary Indexes
----Fallback Secondary Indexes "
So I don't still understand whether you are creating a REPORT or loading DATA.
Then, later, in response to chang_y_chung's solution, you said:
"This works perfectly for vertical concatenation.Thanks a lot!
However when I said HTML, I meant some basic formatting. Eg. if there are 2 txt messages that get concatenated, then i would like to have them on separate lines."
Here's the thing, though -- "basic formatting" doesn't exist in a vacuum. Basic formatting with HTML tags, implies that the HTML tags will be
rendered by an application that knows what the HTML tags mean. The <BR> tag would ONLY "work" when a browser or some software that recognized the <BR> tag inserted the break
when RENDERING the underlying HTML.
So it seems to me that, at one point, you had a solution, one which inserted an HTML <BR> tag into your concatenated text strings (using PROC TRANSPOSE and then concatenating what came from PROC TRANSPOSE).
HTML is "basic formatting" -- do you know whether Teradata tables can actually contain HTML??? Clearly, a text variable in a SAS dataset could conceivably contain HTML tags -- but when you look at the SAS dataset, you will see the "unrendered" HTML tags.
How are you trying to view this output that has a <BR> tag embedded in it??? In a browser??? In Teradata??? Or in SAS??? SAS will NEVER show a <BR> tag as a "line feed" equivalent -- when the <BR> tag is "embedded" in the data. You would have to look at the DATA inside a browser - -to look at your data inside a browser, you would need to create an HTML report from the data.
If I modify the Chang's program just a bit -- at the very end -- if you look in the SAS dataset, you will see the <BR/> tag. If you look at the ODS HTML output report file: c:\temp\look_in_browser.html, however, you will see the equivalent of the "line feed" -- because the BROWSER knows what to do with the HTML <BR> tag.
I believe you already have the answer to your original question, but you will NEVER see the "line feed" inside the SAS dataset.
cynthia
[pre]
/* test data */
data one;
input id txt $;
cards;
1 abc
1 def
2 efg
2 ghi
;
run;
/* transpose to wide */
proc transpose data=one out=wide(drop=_:) prefix=txt;
by id;
var txt;
run;
/* concatenate txt fiels delimited by "<br/>", htmlencoded */
data two;
set wide;
length allTxt $1000;
allTxt = catx("#br#", of txt:);
allTxt = htmlencode(allTxt);
allTxt = tranwrd(allTxt, "#br#", "<br/>");
keep id allTxt;
run;
/* check in LISTING and check in the browser */
/* in LISTING, you will see <br/> in the HTML, you will see the "line feed" */
/* But, in the DATASET, you will ALWAYS see the <br/> tag */
** need PROTECTSPECIALCHARS=OFF so the BREAK tag is respected as a BREAK tag;
ods html file='c:\temp\look_in_browser.html' style=sasweb;
proc print data=two noobs
style(column)={protectspecialchars=off};
run;
ods html close;
[/pre]
Click image to register for webinar
