topic Re: Transpose and arrange two variables in SAS Programming

Transpose and arrange two variables

luvscandy27 — Tue, 28 Sep 2021 15:52:02 GMT

Hello everyone,

I have data like this:

month	type	total_2021	delta
1	ip	$13,523,323	-14%
2	ip	$12,969,525	-16%
3	ip	$13,678,954	-22%
1	ipop	$68,414,734	-7%
2	ipop	$62,715,246	-7%
3	ipop	$68,270,752	-2%
1	op	$54,891,411	-5%
2	op	$49,745,721	-4%
3	op	$54,591,797	5%

What I would like to do, but am not sure how is to get the data like this:

type	1	1_delta	2	2_delta	3	3_delta
ip	$13,523,323	-14%	$12,969,525	-16%	$13,678,954	-22%
ipop	$68,414,734	-7%	$62,715,246	-7%	$68,270,752	-2%
op	$54,891,411	-5%	$49,745,721	-4%	$54,591,797	5%

Any assistance would be great. Below please find the code to create the dataset.

data have;
	input month type $ total_2021 delta;
	format delta percentn10.0 total_2021 dollar12.0;

	datalines;
	1 ip 13523323 -0.14153
	2 ip 12969525 -0.15819
	3 ip 13678954 -0.21653
	1 ipop 68414734 -0.06826
	2 ipop 62715246 -0.06579
	3 ipop 68270752 -0.01973
	1 op 54891411 -0.04825
	2 op 49745721 -0.03827
	3 op 54591797 0.04611
	;
run;

Re: Transpose and arrange two variables

Reeza — Tue, 28 Sep 2021 16:05:45 GMT

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Both of these cover your use case exactly.

Re: Transpose and arrange two variables

pink_poodle — Tue, 28 Sep 2021 16:23:01 GMT

The first step is to create groups by type:

proc sort data=have;
by type;
run;

data h;
set have;
group + 1;
by type;
if first.type then group = 1;
run;

Concatenate groups with either "Total" or "Delta" for transposed column names. I would transpose totals and deltas separately then merge.

e.g., name = catx("Total", "_", group);

Re: Transpose and arrange two variables

Reeza — Tue, 28 Sep 2021 16:37:16 GMT

Why do you think you need a group variable?
Wouldn't Month seems to uniquely identify each month and type identifies the rows uniquely? And you can include multiple variables in both the ID and BY statements, which will then prefix them automatically.

I would recommend using a different naming convetion though, ie Total_Month1, Total_Month2, Delta_Month1, Delta_Month2 which makes it more clear what each variable is. Use labels to get nice labels for reporting.

Re: Transpose and arrange two variables

pink_poodle — Tue, 28 Sep 2021 16:39:55 GMT

I did not notice the Month variable.