Need help in combing cells

[jakkam143aditya]

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.

[lakshmi_74]

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;

[jakkam143aditya]

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

[lakshmi_74]

can you share the output what you are getting?

[jakkam143aditya]

It's something like this:

id output

1                      -There is text here in the original data

2  NA               -This has text before NA

3 

4  NA

Similar for other observations also

[lakshmi_74]

can you share input data aswell?

[jakkam143aditya]

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

[lakshmi_74]

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;

[jakkam143aditya]

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

Can I use the append function here by any chance?

[lakshmi_74]

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?

[jakkam143aditya]

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.

[lakshmi_74]

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