Desktop productivity for business analysts and programmers

transpose a table in a proper way.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

transpose a table in a proper way.

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
id1ID1_value_oneID1_VALUE_TWOid2ID2_VALUE_ONEID2_VALUE_TWODEF1DEF2_desc
110101021251381xxxDefinition of XXX
110101031251640xxxDefinition of XXX
110101041424434xxxDefinition of XXX
1210025120013XYZDefinition of XYZ
1220046230014XYZ Definition of XYZ
Desired table
IdVALUEVALUEDEF1DEF_DESC
1101010xxxDefinition of XXX
21251381xxxDefinition of XXX
1101010xxxDefinition of XXX
31251640xxxDefinition of XXX
1101010xxxDefinition of XXX
41424434xxxDefinition of XXX
121002xyzDefinition of xyz
5120013xyzDefinition of xyz

all numbers are arbitrary.


Accepted Solutions
Solution
‎06-06-2014 08:03 AM
Grand Advisor
Posts: 9,576

Re: transpose a table in a proper way.

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

View solution in original post


All Replies
Grand Advisor
Posts: 17,338

Re: transpose a table in a proper way.

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.

Trusted Advisor
Posts: 1,203

Re: transpose a table in a proper way.

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;

Solution
‎06-06-2014 08:03 AM
Grand Advisor
Posts: 9,576

Re: transpose a table in a proper way.

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

Frequent Contributor
Posts: 89

Re: transpose a table in a proper way.

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 316 views
  • 3 likes
  • 4 in conversation