Text mining and content categorization

Need help in combing cells

Reply
Occasional Contributor
Posts: 9

Need help in combing cells

Hi Everyone,

 

I want to combine couple of cells in excel to one cell without loosing the data. The limit for each excel cell is limited so I have to create multiple variables for it. But I want to combine everything into one variable in sas for my analysis.

 

Data is text, lots of text.

 

Example:

 

Data

 

id var_a var_b var_c var_d

1  aaa..  bbbb.. ccc... ddd..

2  eee..  fff..       ggg.. hhh..

3  iii...     jjj..       kkk..  lll...

 

Result:

 

id output

1  aaa..bbbb..ccc...ddd..

2  eee..fff..ggg..hhh..

3  iii...jjj..kkk..lll...

 

(...) means data is full upto it's limit in excel cell. Each row is a single id so I need not to worry about id's also.

 

Can anyone help me with the problem?

 

I tried something like this:

 

data test;

set present;

length output $32767;

output = catx(' ', var_a, var_b, var_c, var_d);

run;

 

print data test;

run;

 

I am able to create new column but text is not getting copied into it. Just (..) are displaying in every row.

 

 

 

 

Contributor
Posts: 56

Re: Need help in combing cells

data have;
input id var_a $ var_b $ var_c $ var_d $;
datalines;
1 aaa bbbb ccc ddd
2 eee fff ggg hhh
3 iii jjj kkk lll
;
run;
proc sql;
create table want as
select id, catx(' ',var_a,var_b,var_c) from have;
run;

Occasional Contributor
Posts: 9

Re: Need help in combing cells

I already have the data imported, so I skipped the first part.

 

I tried with the following code:

 

proc sql;
create table want as
select id, catx(' ',var_a,var_b,var_c) from have;
run;

 

But the answer in the cells is either blank or na (some cells have it). The data is really huge with text content in var_a, var_b, var_c, var_d. Huge in the sense (in excel each cell limit is 32767 words and it has that much amount, so I created extra columns for additional data). I know I am almost there from my previous methos and this method but couldn't figure out to display the whole text.

 

Thank You

Contributor
Posts: 56

Re: Need help in combing cells

can you share the output what you are getting?

Occasional Contributor
Posts: 9

Re: Need help in combing cells

[ Edited ]

It's something like this:

 

id output

1                      -There is text here in the original data

2  NA               -This has text before NA

4  NA

 

Similar for other observations also

Contributor
Posts: 56

Re: Need help in combing cells

can you share input data aswell?

Occasional Contributor
Posts: 9

Re: Need help in combing cells

It is something like this:

id var_a var_b var_c
1 This is an NA apple
2 US is a great country
3 NA NA NA

And the output after the code is:

id output
1 NA
2
3 NA
Contributor
Posts: 56

Re: Need help in combing cells

data have;
input id var_a $ var_b $ var_c $ var_d $ var_e $;
datalines;
1 This is an NA apple
2 US is a great country
3 NA NA NA
;
run;
data have;
set have;
array var_all var_a--var_e;
do i=1 to dim(var_all);
if var_all(i) eq 'NA' then var_all(i)=' ';
end;
run;
proc print data=have;run;
proc sql;
create table want as
select id,var_a||var_b||var_c||var_d||var_e as var from have;
run;
proc print;run;

Occasional Contributor
Posts: 9

Re: Need help in combing cells

I still have a problem, output column cannot show the required text.

 

Can I use the append function here by any chance?

Contributor
Posts: 56

Re: Need help in combing cells

proc append is used for combine two datasets.
Your case you have to combine columns into a string. Are you reading data properly into a SAS table from excel. If is, then after implementing the above code what outpput you are getting?
Occasional Contributor
Posts: 9

Re: Need help in combing cells

Small confusion on proc append, thank you for clarifying.

 

After implementing the above code, I am getting digit 3 in every cell in the output for my data.

Contributor
Posts: 56

Re: Need help in combing cells

Please check your SAS data whether it is reading '3' aswell.
Ask a Question
Discussion stats
  • 11 replies
  • 266 views
  • 0 likes
  • 2 in conversation