Vertical Concatenation

[SASNEWB]

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!

[Doc_Duke]

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;

[Cynthia_sas]

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]

[deleted_user]

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.

[chang_y_chung_hotmail_com]

If you want to concatenate all the txt values for an id, html encoded and delimited by "<br/>" 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 "<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 */

   proc print data=two noobs;

   run;

   /* on lst

   id        allTxt

   1    a&lt;c<br/>def

   2    efg<br/>ghi

   */

[deleted_user]

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!

[deleted_user]

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!

[Ksharp]

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

[deleted_user]

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!!

[Ksharp]

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

[deleted_user]

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!

[sbb]

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

Scott Barry
SBBWorks, Inc.

[deleted_user]

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!!!

[Ksharp]

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

[deleted_user]

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