topic Transposing in SAS to avoid using Excel? in SAS Programming

Transposing in SAS to avoid using Excel?

SAS93 — Tue, 30 Nov 2021 19:17:33 GMT

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?

Re: Transposing in SAS to avoid using Excel?

maguiremq — Tue, 30 Nov 2021 19:58:11 GMT

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)

Re: Transposing in SAS to avoid using Excel?

PaigeMiller — Tue, 30 Nov 2021 21:32:56 GMT

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.

Re: Transposing in SAS to avoid using Excel?

Kurt_Bremser — Tue, 30 Nov 2021 21:39:23 GMT

Looks like you want a report, so look into ACROSS in PROC REPORT.