BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
luvscandy27
Quartz | Level 8

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
4 REPLIES 4
pink_poodle
Barite | Level 11

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

Reeza
Super User
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.

pink_poodle
Barite | Level 11
I did not notice the Month variable.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 517 views
  • 3 likes
  • 3 in conversation