DATA Step, Macro, Functions and more

Problem in data transpose

Accepted Solution Solved
Reply
Super Contributor
Posts: 266
Accepted Solution

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.


Kindly suggest. Thanks in advance

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
Respected Advisor
Posts: 4,936

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

View solution in original post


All Replies
Solution
‎06-04-2015 11:43 PM
Respected Advisor
Posts: 4,936

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: 266

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,046

Re: Problem in data transpose

Code: Program

data have;
input Org_id;
cards;
11
11
11
11
11
22
22
22
22
22
22
22
22
33
33
33
33
33
33
33
33
33
33
;
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,046

Re: Problem in data transpose

IML version code : Table must be sorted before.

Code: Program

data have;
input Org_id;
cards;
11
11
11
11
11
22
22
22
22
22
22
22
22
33
33
33
33
33
33
33
33
33
33
;
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.

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

Discussion stats
  • 4 replies
  • 226 views
  • 0 likes
  • 3 in conversation