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 |
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
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
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,
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
IML version code : Table must be sorted before.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.