DATA Step, Macro, Functions and more

Transform Long to Wide Format in SAS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 99
Accepted Solution

Transform Long to Wide Format in SAS

[ Edited ]

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  


Accepted Solutions
Solution
‎03-14-2017 12:15 AM
Super User
Posts: 19,805

Re: Transform Long to Wide Format in SAS

Posted in reply to afiqcjohari

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


All Replies
Super User
Posts: 19,805

Re: Transform Long to Wide Format in SAS

Posted in reply to afiqcjohari

What's the logic in combining those tables?

Frequent Contributor
Posts: 99

Re: Transform Long to Wide Format in SAS

[ Edited ]

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.

Super User
Posts: 19,805

Re: Transform Long to Wide Format in SAS

Posted in reply to afiqcjohari

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

 

 

 

Frequent Contributor
Posts: 99

Re: Transform Long to Wide Format in SAS

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.

 

 

 

Super User
Posts: 19,805

Re: Transform Long to Wide Format in SAS

Posted in reply to afiqcjohari

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

Frequent Contributor
Posts: 99

Re: Transform Long to Wide Format in SAS

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.

Super User
Posts: 19,805

Re: Transform Long to Wide Format in SAS

Posted in reply to afiqcjohari

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. 

Frequent Contributor
Posts: 99

Re: Transform Long to Wide Format in SAS

I end up with

 

Capture.PNG

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

 

Capture.PNG

 

 

 

Solution
‎03-14-2017 12:15 AM
Super User
Posts: 19,805

Re: Transform Long to Wide Format in SAS

Posted in reply to afiqcjohari

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;

Frequent Contributor
Posts: 99

Re: Transform Long to Wide Format in SAS

Yes, need to sort it first. I can confirm that.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 237 views
  • 0 likes
  • 2 in conversation