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

I have 3 tables

 

TrYear TrMonth Usage

2017    1            3000

2017    2            4000

 

TrYear TrMonth Type Usage

2017    1             A         1200

2017    1             B         1800

2017    2             A         2000

2017    2             B         2000

 

TrYear TrMonth Active

2017    1            300

2017    2            400

 

I want to transform these three tables in wide format, so that I'll end up with one table like:

 

               Jan-17 Feb-17

Usage     3000   4000

Usage-A 1200  2000

Usage-B 1800  2000

Active      300     400

 

In fact I have more than 3 tables, and i want to combine all these long tables in 1 big wide table  

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Use the TRANSPOSE task or a proc transpose. 

Type goes into BY group. 

Untested:

Proc transpose data=have out=want prefix=D_

by type; *may need to sort first;

var usage;

 

id trminth tryear;

run;

View solution in original post

10 REPLIES 10
Reeza
Super User

What's the logic in combining those tables?

afiqcjohari
Quartz | Level 8

Not sure what you mean by logic. But it's the report template.

 

The challenge here is that, the numbers are gathered through proc sql, so all of them are in long format.

 

I need to combine these tables back into the wide format above.

 

I'm thinking of transforming each long table to wide format and bind them later.

 

But I need to get the transformation right first.

Reeza
Super User

1. First standardize inputs format. 

2. Append - not merge - all tables together

3. Then either use proc report/tabulate to generate report for display OR proc transpose to transpose if you need a dataset. 

 

Your input format should be like the following:

 

Year Month Metric Value

 

 

 

afiqcjohari
Quartz | Level 8

Yes, it's pretty much what I'm heading to now. 

1. I'm standardizing inputs in the long format.

2. proc transpose each one of them

3. append the results from step 2. in the needed variable order.

 

 

 

Reeza
Super User

Notice that my order is switched for 2/3. Think about how many steps you need in your order compared to mine. 

afiqcjohari
Quartz | Level 8

I see your point. But the thing is, some of the tables are based on more than 1 variable.

Refer to the second table, where there are 2 types of usage.

If you know how can I standardise the second type of table into 1, that your steps will make sense.

Reeza
Super User

Table 2 is the exact structure your table should be in, here's some untested code to combine the data shown. 

 

Data combined;

length type $15.;

Set table1 (in=a)

      Table2 (in=b)

      Table3 (rename=active=usage in=C);

 

if a then type='Usage';

if b then type = catx('-', 'Usage', type);

if c then type = 'Active';

 

run;

 

Then transpose or use proc report or tabulate. 

afiqcjohari
Quartz | Level 8

I end up with

 

Capture.PNG

But I'm stuck at transforming this table to similar format as below

 

Capture.PNG

 

 

 

Reeza
Super User

Use the TRANSPOSE task or a proc transpose. 

Type goes into BY group. 

Untested:

Proc transpose data=have out=want prefix=D_

by type; *may need to sort first;

var usage;

 

id trminth tryear;

run;

afiqcjohari
Quartz | Level 8
Yes, need to sort it first. I can confirm that.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 10 replies
  • 2238 views
  • 0 likes
  • 2 in conversation