topic Re: Need help in combing cells in SAS Data Science

Need help in combing cells

jakkam143aditya — Mon, 27 Mar 2017 04:39:19 GMT

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.

Re: Need help in combing cells

lakshmi_74 — Mon, 27 Mar 2017 05:42:20 GMT

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;

Re: Need help in combing cells

jakkam143aditya — Mon, 27 Mar 2017 06:11:16 GMT

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

Re: Need help in combing cells

lakshmi_74 — Mon, 27 Mar 2017 06:34:42 GMT

can you share the output what you are getting?

Re: Need help in combing cells

jakkam143aditya — Mon, 27 Mar 2017 06:38:34 GMT

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

Re: Need help in combing cells

lakshmi_74 — Mon, 27 Mar 2017 06:41:36 GMT

can you share input data aswell?

Re: Need help in combing cells

jakkam143aditya — Mon, 27 Mar 2017 06:46:08 GMT

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

Re: Need help in combing cells

lakshmi_74 — Mon, 27 Mar 2017 06:59:27 GMT

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;

Re: Need help in combing cells

jakkam143aditya — Mon, 27 Mar 2017 14:04:09 GMT

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

Can I use the append function here by any chance?

Re: Need help in combing cells

lakshmi_74 — Mon, 27 Mar 2017 14:54:01 GMT

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?

Re: Need help in combing cells

jakkam143aditya — Mon, 27 Mar 2017 14:55:53 GMT

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.

Re: Need help in combing cells

lakshmi_74 — Mon, 27 Mar 2017 14:58:37 GMT

Please check your SAS data whether it is reading '3' aswell.