BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
o_p
SAS Employee o_p
SAS Employee

Hi All,

How can I concatenate below rows and change it to one row?

I tried to transpose first and then used catx function to concatenate, but the real data will be more than 1K rows.

So, it won't work for 1K columns by transposing the rows and the concatenate all. What should be the shortest way?


Thanks,

Onuralp

data  Test;

input Text $;

datalines;

I

want

to

have

all

these

words

in

one

sentence

;

run;

----

Wanted new column would be like this --> Text2= 'I want to have all these words in one sentence'

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data  Test;
input Text $;
datalines;
I
want
to
have
all
these
words
in
one
sentence
;
run;
data want;
 set test end=last;;
 length want $ 400 ;
 retain want;
 want=catx(' ',want,text);
 if last;
 keep want;
run;

Xia Keshan

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,


Well two points, firstly and most importantly, if you have 1k rows and want to make them one string, then you would only have $2 per row, as the maximum length for strings is $2000.  So this leads onto my example below, it will create an array of $2000. variables which can then be filled up as we go along.

data have (keep=model);
  set sashelp.cars;
run;

data want;
  set have end=last;
  array out_strings{10} $2000.;
  if _n_=1 then curr_array=1;
  retain curr_array out_strings1-out_strings10;
  if length(strip(out_strings{curr_array}))+length(strip(model)) > 1999 then curr_array=curr_array+1;
  out_strings{curr_array}=strip(out_strings{curr_array})||","||strip(model);
  if last then output;
run;

What you will end up with is one row with out_strings1 have the first block of data up to around 1999 characters, then out_strings2 with the next block etc.

Ksharp
Super User
data  Test;
input Text $;
datalines;
I
want
to
have
all
these
words
in
one
sentence
;
run;
data want;
 set test end=last;;
 length want $ 400 ;
 retain want;
 want=catx(' ',want,text);
 if last;
 keep want;
run;

Xia Keshan

art297
Opal | Level 21

Olp: Why do you want to do what you asked about? The forum can probably give you better answers if you state your ultimate goal.

o_p
SAS Employee o_p
SAS Employee

Actually, one of our customers provide us the data in this format. This is a speech to text data which includes words in each row.

So, I've just want to know how it is possible to change it to document format for text mining. Now, I got the solution.

Thanks for asking,

Olp

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
  • 4 replies
  • 564 views
  • 3 likes
  • 4 in conversation