topic Re: How to convert several columns into one column in SAS Enterprise Guide

How to convert several columns into one column

Enne1932 — Mon, 03 Dec 2018 13:17:57 GMT

Hi
I am using SAS Enterprise Guide and was wondering how to transpose several columns into one. For this I need to have the headings transposed in a separate column as in the example to the left.
I would be great if anyone has a good idea how to do so.

Thanks a lot!

Re: How to convert several columns into one column

Kurt_Bremser — Mon, 03 Dec 2018 13:29:42 GMT

Please do NOT post data as pictures. Nobody here is keen to tediously type something off a screenshot so code can be tested.

Post example data in a data step with datalines, as this allows us to recreate your data exactly with a simple copy/paste and submit.

Use a double proc transpose:

proc transpose data=have out=trans(rename=(_name_=varname));
var a b c;
run;

proc transpose data=trans out=want (drop=_name_);
by varname;
var col:;
run;

Re: How to convert several columns into one column

PeterClemmensen — Mon, 03 Dec 2018 13:48:39 GMT

Welcome to the SAS communities 🙂

data have;
input a b c;
datalines;
3   0.5 1
1.3 0.9 1
2   3   2
;

proc transpose data=have out=temp(rename=(_name_=var));
   var a b c;
run;

proc transpose data=temp out=want(drop=_name_);
   by var;
   var col:;
run;

Re: How to convert several columns into one column

Ksharp — Mon, 03 Dec 2018 13:54:52 GMT

data have;
input a b c;
id+1;
datalines;
3   0.5 1
1.3 0.9 1
2   3   2
;

proc transpose data=have out=temp name=var;
by id;
   var a b c;
run;
proc sort data=temp out=want(drop=id);
by var;
run;

Re: How to convert several columns into one column

novinosrin — Mon, 03 Dec 2018 14:14:48 GMT

data have;
input a b c;
datalines;
3   0.5 1
1.3 0.9 1
2   3   2
;


data _null_;
if _n_=1 then do;
 dcl hash H (ordered: "A",multidata:'y') ;
   h.definekey  ("v_name") ;
   h.definedata ("v_name","want") ;
   h.definedone () ;
   end;
set have end=lr;
array t a--c;
length v_name $1;
do over t;
v_name=vname(t);
want=t;
h.add();
end;
if lr then h.output(dataset:'want');
run;

Re: How to convert several columns into one column

Kurt_Bremser — Mon, 03 Dec 2018 14:16:16 GMT

And a simple data step method (all variables need to be of the same type):

data want2;
set have;
array vars{*} _all_;
do i = 1 to dim(vars);
  varname = vname(vars{i});
  value = vars{i};
  output;
end;
keep varname value;
run;

Re: How to convert several columns into one column

Allaluiah — Mon, 03 Dec 2018 14:25:09 GMT

@Kurt_Bremser That may look simple, but safer to user _numeric or _char_ . Also you would need to sort your want2 to actually meet OP's requirement stated in the question

Re: How to convert several columns into one column

Kurt_Bremser — Mon, 03 Dec 2018 14:35:26 GMT

Absolutely. The only method that requires only one step is @novinosrin's, but it may run out of resources if a dataset is very large.

Re: How to convert several columns into one column

Enne1932 — Thu, 06 Dec 2018 14:17:10 GMT

Thanks a lot everyone for your help!

Re: How to convert several columns into one column

Sims — Wed, 27 May 2020 15:00:17 GMT

As a follow-up to this question. My dataset is large to add it after datalines. What can be an alternative in this case?

In fact, my main purpose is to calculate the combined frequency of 2 columns of categorical variables.

Re: How to convert several columns into one column

Kurt_Bremser — Tue, 16 Jun 2020 08:33:37 GMT

Please post your question in a new thread. As you have noticed, nobody saw your post and could therefore answer to it; I only stumbled across it by accident. Please do not hijack question threads that are not yours and are hopelessly out of date.