BookmarkSubscribeRSS Feed
SASNEWB
Calcite | Level 5
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!
20 REPLIES 20
Doc_Duke
Rhodochrosite | Level 12
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
SAS Super FREQ
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
Not applicable
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
Obsidian | Level 7
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
Not applicable
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
Not applicable
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
Super User
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
Not applicable
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
Super User
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Please share your exact SAS code, pasted from your SAS log with all code lines revealed.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
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
Super User
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
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 4934 views
  • 0 likes
  • 7 in conversation