topic Re: multi columns transposed in to single one ( looking for efficient method) in SAS Programming

multi columns transposed in to single one ( looking for efficient method)

sahoositaram555 — Tue, 17 Mar 2020 17:05:15 GMT

Hi everyone,

i have a dataset like posted below:

data abc;
input subjid incl1 incl2 incl3 excl1 excl2 excl3;
cards;
001 1 1 1 0 1 0
002 0 0 0 1 1 0
003 1 0 1 1 0 0
005 1 1 1 1 1 0
006 1 0 1 1 1 0
007 1 0 1 1 1 1
;
run;

i want to produce an output dataset exactly like posted below by using the above mentioned ABC dataset.

SUBJID	TYPE	RESPONSE
001	INCL1	1
001	INCL2	0
001	INCL3	1
001	exCL4	1
001	exCL5	1
001	exCL6	1
002	INCL1	1
002	INCL2	0
002	INCL3	0
002	exCL4	1
002	exCL5	0
002	exCL6	0
003	INCL1	1
003	INCL2	0
003	INCL3	1
003	exCL4	1
003	exCL5	1
003	exCL6	1
004	INCL1	0
004	INCL2	1
004	INCL3	1
004	exCL4	1
004	INCL5	1
004	INCL6	1
005	INCL1	1
005	INCL2	1
005	INCL3	0
005	exCL4	1
005	exCL5	1
005	exCL6	1
006	INCL1	1
006	INCL2	0
006	INCL3	1
006	exCL4	1
006	exCL5	1
006	exCL6	1
007	INCL1	0
007	INCL2	0
007	INCL3	0
007	exCL4	0
007	exCL5	0
007	exCL6	1

Any thoughts how to

Re: multi columns transposed in to single one ( looking for efficient method)

ed_sas_member — Tue, 17 Mar 2020 17:06:53 GMT

Hi @sahoositaram555

Please try this:

proc transpose data=abc out=want (rename=(col1=response)) name=type;
	var incl1 incl2 incl3 excl1 excl2 excl3;
	by subjid;
run;

Best,

Re: multi columns transposed in to single one ( looking for efficient method)

PeterClemmensen — Tue, 17 Mar 2020 17:06:54 GMT

Here is a data step approach

data want (keep= subjid type response);
   set abc;
   array a {*} incl: excl:;
   do _N_ = 1 to dim(a);
      type     = vname(a[_N_]);
      response = a[_N_];
      output;
   end;
run;

Re: multi columns transposed in to single one ( looking for efficient method)

sahoositaram555 — Tue, 17 Mar 2020 17:18:37 GMT

Thank you very much!

Re: multi columns transposed in to single one ( looking for efficient method)

ed_sas_member — Tue, 17 Mar 2020 17:23:12 GMT

You're welcome!