topic Re: Put all the rows in a table in a single column in SAS Programming

Put all the rows in a table in a single column

Datino — Thu, 01 Nov 2018 22:28:52 GMT

I have the following:

data have; 
infile datalines dlm=',' dsd;
   input fruit:$32. veg:$32. num:32.
;
   datalines; 
apple, potato, 30
banana, celery, 50
berry, zucchini, 40
;
run;

How can I append all the columns into a single one so that I get something like this? (I'm aware one column is numeric and would be converted to char)

var

apple

banana

berry

potato

celery

zucchini

Re: Put all the rows in a table in a single column

novinosrin — Thu, 01 Nov 2018 21:55:18 GMT


data have; 
infile datalines dlm=',' dsd;
   input fruit:$32. veg:$32. num:32.
;
   datalines; 
apple, potato, 30
banana, celery, 50
berry, zucchini, 40
;
run;

proc transpose data=have out=w(keep=col1);
by _all_;
var _all_;
run;

/*for alignement correction*/

data want;
set w;
want=left(col1);
drop col1;
run;

EDIT: The above will produce a auto-conversion note in the log

Re: Put all the rows in a table in a single column

novinosrin — Thu, 01 Nov 2018 21:59:49 GMT

/*cleaner method*/

data _have;
set have;
char=put(num,8. -l);
drop num;
run;

proc transpose data=_have out=want(keep=  col1);
by _all_;
var _all_;
run;

Re: Put all the rows in a table in a single column

novinosrin — Thu, 01 Nov 2018 22:12:17 GMT

@Datino I like this as best imho


data want;
set have;
temp=catx(' ',fruit,veg,num);
do _n_=1 to countw(temp,' ');
want=scan(temp,_n_,' ');
output;
end;
keep want;
run;

Make sure to add a length statement for temp like

Length temo $50;

at the top

Re: Put all the rows in a table in a single column

ballardw — Thu, 01 Nov 2018 22:13:50 GMT

Is the output order important, critical or not?

Re: Put all the rows in a table in a single column

Datino — Thu, 01 Nov 2018 22:24:22 GMT

Not important at all.

Re: Put all the rows in a table in a single column

novinosrin — Thu, 01 Nov 2018 22:24:33 GMT

Thank you @ballardw,, you are right to make meaningful grouping as-->

data _null_;
set have end=lr;
if _n_=1 then do;
   dcl hash H (ordered: "A", multidata:'y') ;
   h.definekey  ("vn") ;
   h.definedata ("vn","want") ;
   h.definedone () ;
end;
array t(*) fruit veg;
do i= 1 to 2;
vn=vname(t(I));
want=t(I);
h.add();
end;
want=put(num,8. -l);
vn='num';
h.add();
if lr then h.output(dataset:'want');
run;

Re: Put all the rows in a table in a single column

Peter_C — Thu, 01 Nov 2018 22:43:56 GMT

Data onecolumn ;
Length item $50 ;
INFILE "your data" TRUNCOVER lrecl= 32760 dad;
Input item @@;
Run;

Re: Put all the rows in a table in a single column

Peter_C — Thu, 01 Nov 2018 22:44:41 GMT

D*** autocorrect
For DAD read DSD

Re: Put all the rows in a table in a single column

Peter_C — Thu, 01 Nov 2018 23:02:31 GMT

More code could be added :
If the column number is needed
If numeric validation is needed for column3
If columns might be empty
If columns after the third should be ignored

However, it shouldn't need much more than the code above
(might need to use FLOWOVER rather than TRUNCOVER)

Re: Put all the rows in a table in a single column

Ksharp — Fri, 02 Nov 2018 13:14:34 GMT

data have; 
infile datalines dlm=',' dsd;
   input fruit:$32. veg:$32. num:32.
;
   datalines; 
apple, potato, 30
banana, celery, 50
berry, zucchini, 40
;
run;

proc sql;
create table want as
select fruit from have
union all
select veg from have
union all
select put(num,best. -l) from have;
quit;

Re: Put all the rows in a table in a single column

Peter_C — Fri, 02 Nov 2018 15:06:30 GMT

Would this work as a single pass of WANT if the data step created it as a VIEW?