Quartz | Level 8

## Transpose and arrange two variables

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; `
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

4 REPLIES 4
Super User

## Re: Transpose and arrange two variables

Barite | Level 11

## Re: Transpose and arrange two variables

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);

Super User

## Re: Transpose and arrange two variables

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.

Barite | Level 11

## Re: Transpose and arrange two variables

I did not notice the Month variable.
Discussion stats
• 4 replies
• 512 views
• 3 likes
• 3 in conversation