Transform Long to Wide Format in SAS

[afiqcjohari]

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  

[Reeza]

What's the logic in combining those tables?

[afiqcjohari]

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]

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]

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]

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

[afiqcjohari]

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]

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]

I end up with

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

[Reeza · Accepted solution]

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]

Yes, need to sort it first. I can confirm that.