SAS Programming

Accepted Solution Solved
Reply
SAS Employee o_p
SAS Employee
Posts: 8
Accepted Solution

SAS Programming

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'


Accepted Solutions
Solution
‎09-22-2014 08:34 AM
Super User
Posts: 9,865

Re: SAS Programming

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


All Replies
Super User
Super User
Posts: 7,695

Re: SAS Programming

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.

Solution
‎09-22-2014 08:34 AM
Super User
Posts: 9,865

Re: SAS Programming

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

PROC Star
Posts: 7,431

Re: SAS Programming

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.

SAS Employee o_p
SAS Employee
Posts: 8

Re: SAS Programming

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 207 views
  • 3 likes
  • 4 in conversation