transpose a table in a proper way.

[learner_sas]

Hello Everybody,

I am trying to transpose table in a certain was to do chisquare test. My original table is the way I got data and desired table is the way I want to set up my table for chi square. I am wondering if any of you can help on on this.

thank you

Original table
id1	ID1_value_one	ID1_VALUE_TWO	id2	ID2_VALUE_ONE	ID2_VALUE_TWO	DEF1	DEF2_desc
1	1010	10	2	1251	381	xxx	Definition of XXX
1	1010	10	3	1251	640	xxx	Definition of XXX
1	1010	10	4	1424	434	xxx	Definition of XXX
1	2100	2	5	1200	13	XYZ	Definition of XYZ
1	2200	4	6	2300	14	XYZ	Definition of XYZ
Desired table
Id	VALUE	VALUE	DEF1	DEF_DESC
1	1010	10	xxx	Definition of XXX
2	1251	381	xxx	Definition of XXX
1	1010	10	xxx	Definition of XXX
3	1251	640	xxx	Definition of XXX
1	1010	10	xxx	Definition of XXX
4	1424	434	xxx	Definition of XXX
1	2100	2	xyz	Definition of xyz
5	1200	13	xyz	Definition of xyz

all numbers are arbitrary.

[Reeza]

SAS Learning Module: Reshaping wide to long using a data step

I'd use the method above for something like this, though if you only have two iterations you can probably skip the array part.

The important thing is noting how the output statement works.

[stat_sas]

Hi,

Try this syntax. Code is based on your mentioned requirements.

Regards,

Naeem

data have;
input id1  ID1_value_one  ID1_VALUE_TWO id2 ID2_VALUE_ONE ID2_VALUE_TWO DEF1 $ DEF2_desc $;
datalines;
1 1010 10 2 1251 381 xxx Definition of XXX
1 1010 10 3 1251 640 xxx Definition of XXX
1 1010 10 4 1424 434 xxx Definition of XXX
1 2100 2 5 1200 13 XYZ Definition of XYZ
1 2200 4 6 2300 14 XYZ Definition of XYZ
;

data one;
set have (keep=id1 ID1_value_one ID1_VALUE_TWO DEF1 DEF2_desc rename=(id1=id ID1_value_one=value ID1_VALUE_TWO=value1));
if _n_=1 then new_id=1;
else new_id=_n_+0.5;
run;

data two;
set have (keep=id2 ID2_VALUE_ONE ID2_VALUE_TWO DEF1 DEF2_desc rename=(id2=id ID2_value_one=value ID2_VALUE_TWO=value1));
new_id=id;
run;

data want(drop=new_id);
set one two;
by new_id;
run;

[Ksharp · Accepted solution]

data have;
input id1  ID1_value_one  ID1_VALUE_TWO id2 ID2_VALUE_ONE ID2_VALUE_TWO DEF1 $ DEF2_desc $;
datalines;
1 1010 10 2 1251 381 xxx Definition of XXX
1 1010 10 3 1251 640 xxx Definition of XXX
1 1010 10 4 1424 434 xxx Definition of XXX
1 2100 2 5 1200 13 XYZ Definition of XYZ
1 2200 4 6 2300 14 XYZ Definition of XYZ
;
run;
data want;
 set have;
 array x{*} id1:;
 array y{*} id2:;
 array z{*} Id     VALUE_ONE     VALUE_TWO ;
 do i=1 to dim(x);
  z{i}=x{i};
 end; output;
  do i=1 to dim(y);
  z{i}=y{i};
 end; output;
 keep Id     VALUE_ONE     VALUE_TWO DEF1  DEF2_desc ;
run;

Xia Keshan

[learner_sas]

Thank you , @stat@sas, and specially . I adopted Ksharp Idea to create final table.