BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
learner_sas
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

4 REPLIES 4
Reeza
Super User

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
Ammonite | Level 13

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
Super User
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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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