A is:
count | pct | year | y |
32751 | 8.6 | 2012 | 0 |
35200 | 8.4 | 2013 | 1 |
34422 | 7.8 | 2014 | 2 |
36195 | 7.6 | 2015 | 3 |
39035 | 7.5 | 2016 | 4 |
43060 | 7.6 | 2017 | 5 |
360673 | 6.2 | 2012 | 0 |
357294 | 5.9 | 2013 | 1 |
341996 | 5.3 | 2014 | 2 |
341963 | 5.1 | 2015 | 3 |
351231 | 5.2 | 2016 | 4 |
366421 | 5.3 | 2017 | 5 |
25432 | 6.6 | 2012 | 0 |
25219 | 6.0 | 2013 | 1 |
23947 | 5.4 | 2014 | 2 |
25239 | 5.3 | 2015 | 3 |
27019 | 5.2 | 2016 | 4 |
29103 | 5.1 | 2017 | 5 |
274908 | 4.7 | 2012 | 0 |
257168 | 4.2 | 2013 | 1 |
235824 | 3.6 | 2014 | 2 |
234645 | 3.5 | 2015 | 3 |
237780 | 3.5 | 2016 | 4 |
247493 | 3.6 | 2017 | 5 |
7319 | 1.9 | 2012 | 0 |
9981 | 2.4 | 2013 | 1 |
10475 | 2.4 | 2014 | 2 |
10956 | 2.3 | 2015 | 3 |
12016 | 2.3 | 2016 | 4 |
13957 | 2.5 | 2017 | 5 |
85765 | 1.5 | 2012 | 0 |
100126 | 1.7 | 2013 | 1 |
106172 | 1.6 | 2014 | 2 |
107318 | 1.6 | 2015 | 3 |
113451 | 1.7 | 2016 | 4 |
118928 | 1.7 | 2017 | 5
|
B is :
32751 | 8.6 | 360673 | 6.2 | 25432 | 6.6 | 274908 | 4.7 | 7319 | 1.9 | 85765 | 1.5 |
35200 | 8.4 | 357294 | 5.9 | 25219 | 6.0 | 257168 | 4.2 | 9981 | 2.4 | 100126 | 1.7 |
34422 | 7.8 | 341996 | 5.3 | 23947 | 5.4 | 235824 | 3.6 | 10475 | 2.4 | 106172 | 1.6 |
36195 | 7.6 | 341963 | 5.1 | 25239 | 5.3 | 234645 | 3.5 | 10956 | 2.3 | 107318 | 1.6 |
39035 | 7.5 | 351231 | 5.2 | 27019 | 5.2 | 237780 | 3.5 | 12016 | 2.3 | 113451 | 1.7 |
43060 | 7.6 | 366421 | 5.3 | 29103 | 5.1 | 247493 | 3.6 | 13957 | 2.5 | 118928 | 1.7 |
this is a nice link that explains proc transpose.
Example 3 should answer your needs.
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
reshaping data with 2 variables
what are you transposing on?
you have no column names in B.
That is
count pct count pct count pct
this is a nice link that explains proc transpose.
Example 3 should answer your needs.
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
reshaping data with 2 variables
See if this helps
data have;
input vcount pct year y;
cards;
32751 8.6 2012 0
35200 8.4 2013 1
34422 7.8 2014 2
36195 7.6 2015 3
39035 7.5 2016 4
43060 7.6 2017 5
360673 6.2 2012 0
357294 5.9 2013 1
341996 5.3 2014 2
341963 5.1 2015 3
351231 5.2 2016 4
366421 5.3 2017 5
25432 6.6 2012 0
25219 6.0 2013 1
23947 5.4 2014 2
25239 5.3 2015 3
27019 5.2 2016 4
29103 5.1 2017 5
274908 4.7 2012 0
257168 4.2 2013 1
235824 3.6 2014 2
234645 3.5 2015 3
237780 3.5 2016 4
247493 3.6 2017 5
7319 1.9 2012 0
9981 2.4 2013 1
10475 2.4 2014 2
10956 2.3 2015 3
12016 2.3 2016 4
13957 2.5 2017 5
85765 1.5 2012 0
100126 1.7 2013 1
106172 1.6 2014 2
107318 1.6 2015 3
113451 1.7 2016 4
118928 1.7 2017 5
;
proc transpose data=have out=w;
by year notsorted;
var vcount pct;
run;
proc sort data=w out=ww(keep=year col1);
by year;
run;
proc transpose data=ww out=want(drop=_name_);
by year;
var col1;
run;
Why?
That looks like a report instead of a dataset.
Easy to do in PROC REPORT, but your source data has no grouping variable.
Looks like a new group starts whenever Y=0.
data have;
input count pct year y @@;
group+(y=0);
cards;
32751 8.6 2012 0 35200 8.4 2013 1 34422 7.8 2014 2 36195 7.6 2015 3 39035 7.5 2016 4 43060 7.6 2017 5
360673 6.2 2012 0 357294 5.9 2013 1 341996 5.3 2014 2 341963 5.1 2015 3 351231 5.2 2016 4 366421 5.3 2017 5
25432 6.6 2012 0 25219 6.0 2013 1 23947 5.4 2014 2 25239 5.3 2015 3 27019 5.2 2016 4 29103 5.1 2017 5
;
proc report data=have ;
column group year,(count pct) ;
define group / group ;
define year / across ;
run;
I need further process.
@walterwang wrote:
I need further process.
Then keep it in the form you have where it is usable.
How to save the ourtput from tabulate as a data set? Any idea, thanks
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.