topic Re: Vertical Concatenation in SAS Programming

Vertical Concatenation

SASNEWB — Thu, 13 Jan 2011 13:44:09 GMT

I am trying to vertically concatenate all the txt messages in my SAS dataset by an id and 2 more columns into one msg.
So if my table is initially like this:
1 abc
1 def
2 efg
2 ghi

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.
1 abc
def
2 efg
ghi

I read about vertical concatenation of matrices, but not aware of how to convert a sas dataset into a mtrix. Also, I want to provide some basic HTML formatting so that the text messages is readable.

Thanks!

Re: Vertical Concatenation

Doc_Duke — Thu, 13 Jan 2011 14:35:38 GMT

As I understand this, there are three ID variables and one text variable in the SAS dataset and you want a report, not another SAS dataset. This may be easiest with a DATA step. You will need to do some additional reading. Look at something like this, assuming the data are already sorted.

ODS HTML (add additional HTML parameters);

DATA _NULL_;
SET mydata;
BY id1 id2 id3;
FILE PRINT (additional parameters if needed);
IF first.id3 THEN PUT id1 id2 id3 textvar;
ELSE PUT textvar;
IF last.id3 THEN PUT (additional pretty-print for readability);
RUN;

ODS _ALL_ CLOSE;

Re: Vertical Concatenation

Cynthia_sas — Thu, 13 Jan 2011 15:06:19 GMT

Hi:
You might use a DATA step, but I think that, for a report (since you can't put line breaks or carriage returns in a dataset), PROC REPORT will do quite nicely. Especially if you use the JOURNAL style, which removes interior table lines, it will look like 'def' is stacked vertically under 'abc'.

cynthia
[pre]
data mydata;
infile datalines;
input id txt $;
return;
datalines;
1 abc
1 def
2 efg
2 ghi
;
run;

ods listing;
ods html file='c:\temp\output\use_order.html' style=journal;

proc report data=mydata nowd;
column id txt;
define id / order;
define txt / display;
run;

ods html close;
[/pre]

Re: Vertical Concatenation

deleted_user — Thu, 13 Jan 2011 15:46:11 GMT

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 tbale.

Re: Vertical Concatenation

chang_y_chung_hotmail_com — Thu, 13 Jan 2011 16:22:46 GMT

If you want to concatenate all the txt values for an id, html encoded and delimited by " " then a simple data step would do. Just give the allTxt variable a long enough length:

/* test data */

data one;

input id txt $;

cards;

1 a<c

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 " ", htmlencoded */

data two;

set wide;

length allTxt $1000;

allTxt = catx("#br#", of txt:);

allTxt = htmlencode(allTxt);

allTxt = tranwrd(allTxt, "#br#", " ");

keep id allTxt;

run;

/* check */

proc print data=two noobs;

run;

/* on lst

id allTxt

1 a<c def

2 efg ghi

Re: Vertical Concatenation

deleted_user — Fri, 14 Jan 2011 03:56:39 GMT

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.

Any pointers are appreciated.

Thank you!

Re: Vertical Concatenation

deleted_user — Fri, 14 Jan 2011 04:14:18 GMT

Also I would like to have a column 'TYPE. As per this type, can we have a Heading line before the messages in that TYPE.

Example:

ID Type Txt
1 Class ABC is my class
1 Teacher DEF is my teacher

Output should look like:
ID xt
1 Class:
ABC is my class
Teacher:
DEF is my teacher

Thank you!

Re: Vertical Concatenation

Ksharp — Fri, 14 Jan 2011 07:28:26 GMT

Hi.
Just as Cynthia mentioned before.
Let me show your Power of Proc report.

[pre]
data temp;
input ID $ Type $ Txt & $20.;
cards;
1 Class ABC is my class
1 Teacher DEF is my teacher
2 cClass aABC is my class
2 tTeacher dDEF is my teacher
;
run;
ods html file='c:\test.html' style=sasweb ;
proc report data=temp nowd out=out;
column id type txt;
define id /order noprint;
define type /order noprint;
define txt /order noprint;
compute id;
if id eq lag(id) then len=4 ;
else len=0;
endcomp;
compute before /style={just=left};
line 'ID ' 'xT';
endcomp;
compute after txt/style={just=left};
line id $varying. len type $10.;
line txt $20.;
endcomp;
run;
ods html close;

[/pre]

Ksharp

Re: Vertical Concatenation

deleted_user — Fri, 14 Jan 2011 15:34:42 GMT

Appreciate your help on creating a proc.
However when I look at the OUT dataset, it looks like this and I will be loading this dataset back to Teardata.
ID Type Txt

1 Class ABC is my class
1 Class ABC is my class
1 Teacher DEF is my teacher
1 Teacher DEF is my teacher
2 cClass aABC is my class
2 cClass aABC is my class
2 tTeacher dDEF is my teacher
2 tTeacher dDEF is my teacher

The OUT dataset shd really look like this:
ID Txt

1 Class
ABC is my class
Teacher
DEF is my teacher
2 cClass
aABC is my class
tTeacher
dDEF is my teacher

Basically I need to consolidate all Class data together and Teacher data tigether under each ID with a heading (CLass/Teacher) to identify the data.Also the line breaks, so that all data is not on one line.

Thank you!!

Re: Vertical Concatenation

Ksharp — Mon, 17 Jan 2011 03:43:27 GMT

Hi.
Do you mean in the dataset?But you have two variable id and txt,then
'ABC is my class' will be id or txt?

[pre]
data temp;
input ID $ Type $ Txt & $20.;
cards;
1 Class ABC is my class
1 Teacher DEF is my teacher
2 cClass aABC is my class
2 tTeacher dDEF is my teacher
;
run;
data temp;
set temp;
_txt=txt;
txt=type; if id eq lag(id) then call missing(id);
output;
txt=_txt; call missing(id);
output;
drop _txt type;
run;
[/pre]

Ksharp

Re: Vertical Concatenation

deleted_user — Tue, 18 Jan 2011 15:29:28 GMT

Thank you for your reponse.
My result however created separate records for the Type variable too.So I am ending up with 4 records per id when I would like to see all these 4 records merged together but on separate lines.How do I merge them because I do not see the ID for 3 records out of the 4?
I need to upload this result dataset back to teradata as it looks.

Id Txt
1 Class
ABC is my class
Teacher
Teacher DEF is my teacher
2 Class
aABC is my class
tTeacher
dDEF is my teacher

Thank you!

Re: Vertical Concatenation

sbb — Tue, 18 Jan 2011 15:38:44 GMT

Please share your exact SAS code, pasted from your SAS log with all code lines revealed.

Scott Barry
SBBWorks, Inc.

Re: Vertical Concatenation

deleted_user — Tue, 18 Jan 2011 17:08:04 GMT

80 rsubmit;
NOTE: Remote submit to PRSAS1 commencing.

470 data prod.CS;
471 set prod.CS_mrg;
472 _txt=allTxt;
473 allTxt=type;
474 if ID eq lag(ID) then call missing(ID);
475 output;
476 allTxt=_txt;
477 call missing(ID);
478 output;
479 drop _txt type;
480 run;

NOTE: There were 2 observations read from the data set prod.CS_MRG.
NOTE: The data set prod.CS has 4 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

NOTE: Remote submit to PRSAS1 complete.

This is how my input data looks:
Id Type Alltxt
1 Class ABC is my Class
1 Teacher DEF is my teacher.

And my current output has 4 separate records which I would like to be merged per Id:
Id Alltxt
1 Class
ABC is my Class
Teacher
DEF is my teacher.

Thank you!!!

Re: Vertical Concatenation

Ksharp — Wed, 19 Jan 2011 03:12:53 GMT

you can generate these output separately.
you say 'merge' is to mean horizontal merge or vertical merge?

If it is horizontal merge ,you can use ' count+1' statement to make a merge variable.
If it is vertical merge, you can use ' data all; set a b c d ; run;' to union all datasets.

Ksharp

Re: Vertical Concatenation

deleted_user — Wed, 19 Jan 2011 14:58:08 GMT

ID Type Txt

1 Class ABC is my class
1 Class ABC is my class
1 Teacher DEF is my teacher
1 Teacher DEF is my teacher
2 cClass aABC is my class
2 cClass aABC is my class
2 tTeacher dDEF is my teacher
2 tTeacher dDEF is my teacher

The OUT dataset shd really look like this. I want to merge the records by id.
So if there are 4 records for ID 1, they should vertically concatenate into one.
And my SAS code which I printed above, does not create the ID , when ideally it should, so that the merge on d would be easier.
ID Txt

1 Class
ABC is my class
Teacher
DEF is my teacher
2 cClass
aABC is my class
tTeacher
dDEF is my teacher

Re: Vertical Concatenation

Cynthia_sas — Wed, 19 Jan 2011 18:15:32 GMT

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 tag would ONLY "work" when a browser or some software that recognized the 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 &LT;BR&GT; 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 tag embedded in it??? In a browser??? In Teradata??? Or in SAS??? SAS will NEVER show a tag as a "line feed" equivalent -- when the 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 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 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 " ", htmlencoded */
data two;
set wide;
length allTxt $1000;
allTxt = catx("#br#", of txt:);
allTxt = htmlencode(allTxt);
allTxt = tranwrd(allTxt, "#br#", " ");
keep id allTxt;
run;

/* check in LISTING and check in the browser */
/* in LISTING, you will see in the HTML, you will see the "line feed" */
/* But, in the DATASET, you will ALWAYS see the 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]

Re: Vertical Concatenation

deleted_user — Thu, 20 Jan 2011 21:41:50 GMT

You are right Cynthia. For a line break what I need is just
and that should work.This is one last piece I need to address.
I used the code for line break as well as for adding a heading. I have my output looking like this:
Id Txt
1 Class
ABC is my class
Teacher
DEF is my teacher

So I was thinking of using a MERGE BY to merge all these 4 separate records into one. But for that I need to have ID on each of these 4 records and currently the ID is missing in the last 3. The code that actually adds the heading is given below. Can you help me change this code so that I get an ID in every record so taht merging would be easier?

data temp;
input ID $ Type $ Txt & $20.;
cards;1 Class ABC is my class1 Teacher DEF is my teacher2 cClass aABC is my class2 tTeacher dDEF is my teacher;
run;

data temp;
set temp;
_txt=txt;
txt=type;
if id eq lag(id) then call missing(id);
output; txt=_txt;
call missing(id);
output; drop _txt type;
run;

Thank you!

Re: Vertical Concatenation

Cynthia_sas — Thu, 20 Jan 2011 23:25:29 GMT

Hi:
It is very hard to read your posting. I suspect that you used or some other HTML tags in your code that made subsequent formatting look bad.

This is a useful forum posting:
http://support.sas.com/forums/thread.jspa?messageID=27609毙 about how, to use the [pre] and the [/pre] tags around your code in order to avoid having it become virtually unreadable.

I do think, however, that you are confusing OUTPUT and INPUT. INPUT data is what you read -into- a SAS dataset, then you do some analysis or graphing with the SAS dataset and then you create OUTPUT--either an OUTPUT dataset or an OUTPUT report.

My original understanding was that your INPUT data looked like any of these previous examples you have posted:
Initial INPUT on Jan 13:
[pre]
1 abc
1 def
2 efg
2 ghi
[/pre]

Initial desired OUTPUT on Jan 13:
[pre]
1 abc
def
2 efg
ghi
[/pre]

Then, you posted slightly different input data on Jan 13, Jan 14, Jan 19 and now, today, you say that you have INPUT that, to me, looks very like the OUTPUT you said you wanted.

The way I read your post, you said that (I cleaned up the line breaks)
" I have my output looking like this:
[pre]
Id Txt
1 Class
ABC is my class
Teacher
DEF is my teacher
[/pre]

And then your post continues with:
"I was thinking of using a MERGE BY to merge all these 4 separate records into one. But for that I need to have ID on each of these 4 records and currently the ID is missing in the last 3."

To which I respond, HUH???? What happened to your original INPUT data that you showed us where ID was on every row in the original INPUT dataset -- back on Jan 13 or 14????? Why would you use your OUTPUT to do any type of MERGE at all???

I am so confused by what you have, what your INPUT really looks like, how you're reading that INPUT data and what OUTPUT you want.

Now you want to merge the OUTPUT back into a form that looks like the original INPUT you showed us on Jan 13??? I am thoroughly confused.

cynthia

Re: Vertical Concatenation

deleted_user — Fri, 21 Jan 2011 03:04:06 GMT

Let me explain again. I tried to simplify the data wherever possible earlier.My I/P data looks like this and there are many otehr columns but they do not matter for my O/P.In my I/P shown below all the 4 are separate records.

Id Type Txt
1 Class ABC is my Class
1 Class I like my class
1 Teacher DEF is my teacher
1 Teacher DEF is my teacher

There could be multiple rows for each of the TYPE's but I have shown only 2 per TYPE for simplicity.
When I say I need my O/P as below, I mean one record per ID, with a heading to differentiate between the various TYPE's.

ID Text
1 Class
---->HEading
ABC is my class

I like my class

Teacher
---> Another heading
DEF is my teacher

I like my teacher

I first did a transpose on my I/P by ID and Txt AND var as Txt. Then I used Then i used the allTxt = catx("
", of txt:). And then I used the code below to get a heading for each type.
data temp;
set temp;
_txt = txt;
txt = type;
if id eq lag(id) then call missing(id);
output;
txt = _txt;
call misiing(id);
output;
drop _txt type;
run;

Now what I have achieved till now is given below, which are 4 separate records and I would like to merge them into one.

Id Text
1 Class ----> HEading is a separate record.
Abc is my class
I like my class ->2'nd record
Teacher -----> Anotehr heading a 3'rd record
DEF is my teacher
I like my teacher-> 4'th record

Here the id is not populated in the last 3 records. Now if I have to merge BY id , I would need the id in all records. So can I make a change to the code I posted above so that I get an ID in my output for every record.

Thank you!
I have no idea why my post looks like this!! Message was edited by: sasnewbee

Re: Vertical Concatenation

Ksharp — Fri, 21 Jan 2011 03:26:14 GMT

Hi.
>However the ID is not populate in the last 3 records.

If you want these id, can remove 'call missing' statement.

[pre]
data temp;
input ID $ Type $ Txt & $20.;
cards;
1 Class ABC is my class
1 Teacher DEF is my teacher
2 cClass aABC is my class
2 tTeacher dDEF is my teacher
;
run;
data temp;
set temp;
_txt=txt;
txt=type;
output;
txt=_txt;
output;
drop _txt type;
run;
[/pre]

Ksharp