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.
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;
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
can you share the output what you are getting?
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
can you share input data aswell?
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;
I still have a problem, output column cannot show the required text.
Can I use the append function here by any chance?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.