topic Re: Transform Long to Wide Format in SAS in SAS Programming

Transform Long to Wide Format in SAS

afiqcjohari — Fri, 10 Mar 2017 02:23:55 GMT

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

Re: Transform Long to Wide Format in SAS

Reeza — Fri, 10 Mar 2017 02:37:02 GMT

What's the logic in combining those tables?

Re: Transform Long to Wide Format in SAS

afiqcjohari — Fri, 10 Mar 2017 02:52:23 GMT

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.

Re: Transform Long to Wide Format in SAS

Reeza — Fri, 10 Mar 2017 03:05:30 GMT

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

Re: Transform Long to Wide Format in SAS

afiqcjohari — Fri, 10 Mar 2017 04:07:55 GMT

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.

Re: Transform Long to Wide Format in SAS

Reeza — Fri, 10 Mar 2017 05:38:49 GMT

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

Re: Transform Long to Wide Format in SAS

afiqcjohari — Fri, 10 Mar 2017 06:14:38 GMT

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.

Re: Transform Long to Wide Format in SAS

Reeza — Fri, 10 Mar 2017 06:47:42 GMT

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.

Re: Transform Long to Wide Format in SAS

afiqcjohari — Tue, 14 Mar 2017 02:01:36 GMT

I end up with

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

Re: Transform Long to Wide Format in SAS

Reeza — Tue, 14 Mar 2017 02:25:21 GMT

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;

Re: Transform Long to Wide Format in SAS

afiqcjohari — Tue, 14 Mar 2017 04:15:38 GMT

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