topic Re: Problem in data transpose in SAS Programming

Problem in data transpose

Aman4SAS — Fri, 05 Jun 2015 03:10:35 GMT

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

Re: Problem in data transpose

PGStats — Fri, 05 Jun 2015 03:43:22 GMT

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;

Re: Problem in data transpose

Aman4SAS — Tue, 09 Jun 2015 09:38:04 GMT

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

and i looking for dataset like :

Org_id _11 _22 _33

11 1 0 0

22 0 1 0

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;

run;

its not working.

Kindly suggest,

Thanks & Regards,

Re: Problem in data transpose

Ksharp — Tue, 09 Jun 2015 12:21:14 GMT

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

Re: Problem in data transpose

Ksharp — Tue, 09 Jun 2015 12:51:39 GMT

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