## transpose a table in a proper way.

Solved
Frequent Contributor
Posts: 89

# 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 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.

Accepted Solutions
Solution
‎06-06-2014 08:03 AM
Super User
Posts: 10,849

## 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

All Replies
Super User
Posts: 23,998

## 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.

Posts: 1,270

## 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
Super User
Posts: 10,849

## 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 and locked.