I have a dataset table that I want to ultimately transpose:
Year | PCP_Visit_past_year | n | Row_Percent | Lower_CL | Upper_CL |
---|---|---|---|---|---|
2016 | Yes | 628 | 84.6800 | 81.2606 | 88.0995 |
2016 | No | 110 | 14.5566 | 11.1867 | 17.9265 |
2016 | Never vist | 7 | 0.7634 | 0.0458 | 1.4809 |
2017 | Yes | 805 | 87.8194 | 84.9743 | 90.6645 |
2017 | No | 108 | 11.8672 | 9.0415 | 14.6928 |
2017 | Never vist | 4 | 0.3135 | 0.0000 | 0.6998 |
2018 | Yes | 583 | 86.1388 | 82.9866 | 89.2909 |
2018 | No | 82 | 13.2549 | 10.1490 | 16.3609 |
2018 | Never vist | 4 | 0.6063 | 0.0000 | 1.2480 |
2019 | Yes | 537 | 84.6572 | 80.5744 | 88.7401 |
2019 | No | 100 | 14.9445 | 10.8877 | 19.0013 |
2019 | Never vist | 6 | 0.3983 | 0.0000 | 0.9672 |
2020 | Yes | 492 | 84.5874 | 80.6830 | 88.4919 |
2020 | No | 94 | 14.3637 | 10.5594 | 18.1679 |
2020 | Never vist | 8 | 1.0489 | 0.0061 | 2.0916 |
I'm going to drop the frequency first, then I plan to use Excel to concatenate Row_Percent with the lower/upper CL --> % (95% CL) <-- and finally change this around to show:
PCP_Visit_past_year | 2016 | 2017 | 2018 | 2019 | 2020 |
Yes | % (95% CL) | % (95% CL) | % (95% CL) | % (95% CL) | % (95% CL) |
No | % (95% CL) | .. | .. | .. | .. |
Never vist | % (95% CL) | .. | .. | .. | .. |
But being the lazy person I am, there are probably 2-3 dozen tables I have to do this for--so how do I use SAS to make this easier?
Probably more efficient ways of doing this -- kind of a mess with the `CATX` function.
data have;
infile datalines delimiter = "," dsd;
input Year PCP_Visit_past_year :$10. n Row_Percent Lower_CL Upper_CL;
datalines;
2016,Yes,628,84.6800,81.2606,88.0995
2016,No,110,14.5566,11.1867,17.9265
2016,Never vist,7,0.7634,0.0458,1.4809
2017,Yes,805,87.8194,84.9743,90.6645
2017,No,108,11.8672,9.0415,14.6928
2017,Never vist,4,0.3135,0.0000,0.6998
2018,Yes,583,86.1388,82.9866,89.2909
2018,No,82,13.2549,10.1490,16.3609
2018,Never vist,4,0.6063,0.0000,1.2480
2019,Yes,537,84.6572,80.5744,88.7401
2019,No,100,14.9445,10.8877,19.0013
2019,Never vist,6,0.3983,0.0000,0.9672
2020,Yes,492,84.5874,80.6830,88.4919
2020,No,94,14.3637,10.5594,18.1679
2020,Never vist,8,1.0489,0.0061,2.0916
;
run;
proc sql;
create table have_1 as
select
year,
pcp_visit_past_year,
catx(" ", cat(row_percent, "%"), cat("(", lower_cl), cat(" - ", upper_cl, ")")) as cl
from
have
order by
pcp_visit_past_year, year;
quit;
proc transpose
data = have_1
out = want (drop = _name_);
id year;
by pcp_visit_past_year;
var cl;
run;
You can't have variable names with only numbers. `PROC TRANSPOSE` inserts an `_` to accommodate this issue.
Obs | PCP_Visit_past_year | _2016 | _2017 | _2018 | _2019 | _2020 |
---|---|---|---|---|---|---|
1 | Never vist | 0.7634% (0.0458 - 1.4809) | 0.3135% (0 - 0.6998) | 0.6063% (0 - 1.248) | 0.3983% (0 - 0.9672) | 1.0489% (0.0061 - 2.0916) |
2 | No | 14.5566% (11.1867 - 17.9265) | 11.8672% (9.0415 - 14.6928) | 13.2549% (10.149 - 16.3609) | 14.9445% (10.8877 - 19.0013) | 14.3637% (10.5594 - 18.1679) |
3 | Yes | 84.68% (81.2606 - 88.0995) | 87.8194% (84.9743 - 90.6645) | 86.1388% (82.9866 - 89.2909) | 84.6572% (80.5744 - 88.7401) | 84.5874% (80.683 - 88.4919) |
I have a dataset table that I want to ultimately transpose:
Why? What is the benefit?
What is the next step after the transpose? If you want the final output as a table (such as one you can put into a report), then this transpose is completely unnecessary as PROC REPORT can put your calendar years as columns, and it will be a whole lot easier than transposing and then creating some sort of report. If you need a SAS data set for further analysis, DO NOT TRANSPOSE as this makes the subsequent programming much much much much much much harder — muchly harder.
So take the simple approach, don't transpose this data.
Looks like you want a report, so look into ACROSS in PROC REPORT.
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.