## Problem in data transpose

Solved
Super Contributor
Posts: 271

# Problem in data transpose

2

I have a data where i need to transpose that for futher statistical analysis.

sample data and desired output are below. [1111,2222,3333,4444,5555 are org_number and its in excel, Major concern is there is near about 9000 organizations. there is no column names.]

i tried to do with proc transpose but couldnt get correct output.

Data Table:

 1111 2222 3333 4444 5555 6666 713 701 291 674 706 180 232 665 789 408 101 501 724 529 722 721 360 850 767 928 705 582 854 674 975 741 352 248 489 914 991 386 994 392 412 364 977 234 435 945 335 958 722 959 959 164 598 624 134 461 459 518 980 715 227 664 834 567 800 444 201 548 368 851 128 802 695 124 203 323 239 178

Desire Output:

 1111 713 1111 232 1111 724 1111 767 1111 975 1111 991 1111 977 1111 722 1111 134 1111 227 1111 201 1111 695 2222 701 2222 665 2222 529 2222 928 2222 741 2222 386 2222 234 2222 959 2222 461 2222 664 2222 548 2222 124 3333 291 3333 789 3333 722 3333 705 3333 352 3333 994 3333 435 3333 959 3333 459 3333 834 3333 368 3333 203 4444 674 4444 408 4444 721 4444 582 4444 248 4444 392 4444 945 4444 164 4444 518 4444 567 4444 851 4444 323 5555 706 5555 101 5555 360 5555 854 5555 489 5555 412 5555 335 5555 598 5555 980 5555 800 5555 128 5555 239 6666 180 6666 501 6666 850 6666 674 6666 914 6666 364 6666 958 6666 624 6666 715 6666 444 6666 802 6666 178

Accepted Solutions
Solution
‎06-04-2015 11:43 PM
Posts: 5,543

## Re: Problem in data transpose

Treat the first line as column headers (an underscore will be added in front of the number), then you can transpose (sort of) :

/* You should get a dataset like this one */

data test;

input _1111 _2222 _3333 _4444 _5555 _6666;

datalines;

713 701 291 674 706 180

232 665 789 408 101 501

724 529 722 721 360 850

767 928 705 582 854 674

975 741 352 248 489 914

991 386 994 392 412 364

977 234 435 945 335 958

722 959 959 164 598 624

134 461 459 518 980 715

227 664 834 567 800 444

201 548 368 851 128 802

695 124 203 323 239 178

;

/* Add a BY variable for transposition */

data test2;

set test;

obs = _n_;

run;

proc transpose data=test2 out=test3(drop=obs);

by obs;

var _:;

run;

/* Transform the column headers back to numbers */

data test4;

set test3;

col0 = input(substr(_name_, 2), best.);

drop _name_;

run;

PG

PG

All Replies
Solution
‎06-04-2015 11:43 PM
Posts: 5,543

## Re: Problem in data transpose

Treat the first line as column headers (an underscore will be added in front of the number), then you can transpose (sort of) :

/* You should get a dataset like this one */

data test;

input _1111 _2222 _3333 _4444 _5555 _6666;

datalines;

713 701 291 674 706 180

232 665 789 408 101 501

724 529 722 721 360 850

767 928 705 582 854 674

975 741 352 248 489 914

991 386 994 392 412 364

977 234 435 945 335 958

722 959 959 164 598 624

134 461 459 518 980 715

227 664 834 567 800 444

201 548 368 851 128 802

695 124 203 323 239 178

;

/* Add a BY variable for transposition */

data test2;

set test;

obs = _n_;

run;

proc transpose data=test2 out=test3(drop=obs);

by obs;

var _:;

run;

/* Transform the column headers back to numbers */

data test4;

set test3;

col0 = input(substr(_name_, 2), best.);

drop _name_;

run;

PG

PG
Super Contributor
Posts: 271

## Re: Problem in data transpose

Hi apology in advance for posting my question here,   I am not able to post my question in forum as its showing error that "NOT allowed"

Hi All,

i have a data set like :

Org_id

11

11

11

11

11

22

22

22

22

22

22

22

22

33

33

33

33

33

33

33

33

33

33

and i looking for dataset like :

Org_id _11 _22 _33

11 1 0 0

11 1 0 0

11 1 0 0

11 1 0 0

11 1 0 0

22 0 1 0

22 0 1 0

22 0 1 0

22 0 1 0

22 0 1 0

22 0 1 0

22 0 1 0

22 0 1 0

33 0 0 1

33 0 0 1
33 0 0 1

33 0 0 1

33 0 0 1

33 0 0 1

33 0 0 1

33 0 0 1

33 0 0 1

33 0 0 1

i trying code is :

%let org_list1=11,22,33 (in main code i have created it by using proc sql and separated bby ",")

Data Temp;

set mmm_test;

do i =1 to 2;

if org_id= input(scan("&org_list1",1,","),12.) then

do; %let var= _%scan("&org_list1",i,",");

&var=1;

end;

else do;

%let var= _%scan("&org_list1",i,",");

&var=0;

end;

end;

run;

its not working.

Kindly suggest,

Thanks & Regards,

Super User
Posts: 10,787

## Re: Problem in data transpose

### Code: Program

`data have;input Org_id;cards;1111111111222222222222222233333333333333333333;run;data temp; set have; retain v 1; n+1;run;proc transpose data=temp out=temp1(drop=n _name_) prefix=_;by n;id Org_id;var v;copy Org_id;run;proc stdize data=temp1 out=want reponly missing=0;run;`

Xia Keshan

Super User
Posts: 10,787

## Re: Problem in data transpose

IML version code : Table must be sorted before.

### Code: Program

`data have;input Org_id;cards;1111111111222222222222222233333333333333333333;run;proc iml;use have;read all var {Org_id};close have;m=design(Org_id);m=Org_id||m;col='Org_id'||char(unique(Org_id));create want from m[c=col];append from m;close want;quit;`

Xia Keshan

🔒 This topic is solved and locked.