SAS Hive SQL (Hadoop) version of Proc Transpose?

Question

I was wondering if there is a version of 'Proc Transpose' in SAS Hive SQL (Hadoop) ?

Otherwise I can see the only other (long winded) way is creating a lot of separate tables to then join back together, which I'd rather avoid.

Any assistance most welcome!

Sample table to Transpose > Intention to put Month along the top of the table so the rates are split by month:

+------+-------+----------+----------+-------+
| YEAR | MONTH |   Geog   | Category | Rates |
+------+-------+----------+----------+-------+
| 2018 |     1 | National | X        |    32 |
| 2018 |     1 | National | Y        |    43 |
| 2018 |     1 | National | Z        |    47 |
| 2018 |     1 | Regional | X        |    52 |
| 2018 |     1 | Regional | Y        |    38 |
| 2018 |     1 | Regional | Z        |    65 |
| 2018 |     2 | National | X        |    63 |
| 2018 |     2 | National | Y        |    14 |
| 2018 |     2 | National | Z        |    34 |
| 2018 |     2 | Regional | X        |    90 |
| 2018 |     2 | Regional | Y        |    71 |
| 2018 |     2 | Regional | Z        |    69 |
+------+-------+----------+----------+-------+

Sample output:

+------+----------+----------+----+----+
| YEAR |   Geog   | Category | 1  | 2  |
+------+----------+----------+----+----+
| 2018 | National | X        | 32 | 63 |
| 2018 | National | Y        | 43 | 14 |
| 2018 | National | Z        | 47 | 34 |
| 2018 | Regional | X        | 52 | 90 |
| 2018 | Regional | Y        | 38 | 71 |
| 2018 | Regional | Z        | 65 | 69 |
+------+----------+----------+----+----+

if yiu can give sample input and output someone may be able to help you. — Kiran

Richard Richard · Accepted Answer · 2018-11-26T12:38:47

The typical wallpaper SQL technique for transposing (or pivoting) is a group+transform to pivot case statements sub-query within a group aggregating query that collapses the sub-query. The group represents a single resultant pivot row.

For example your group is year, geog, category and min is used to collapse:

proc sql;
  create view want_pivot as
  select year, geog, category
  , min(rate_m1) as rate_m1
  , min(rate_m2) as rate_m2
  from
  ( select
    year, geog, category
    , case when month=1 then rates end as rate_m1
    , case when month=2 then rates end as rate_m2
    from have
  )
  group by year, geog, category
  ;

Here is the same concept, a little more generically where data is repeated within the group at the detail level and mean is used to collapse over the repeats.

data have;
input id name $ value;
datalines;
1 a 1
1 a 2 
1 a 3
1 b 2
1 c 3
2 a 2
2 d 4
2 b 5
3 e 1
run;

proc sql;
  create view have_pivot as 
  select
  id
  , mean(a) as a
  , mean(b) as b
  , mean(c) as c
  , mean(d) as d
  , mean(e) as e
  from
  (
    select
      id
      , case when name='a' then value end as a
      , case when name='b' then value end as b
      , case when name='c' then value end as c
      , case when name='d' then value end as d
      , case when name='e' then value end as e
    from have
  )
  group by id
;
quit;

When the column names are not known apriori, you will need to write a code generator that passes over all the data to determine the name values, writes the wall paper query which will perform a second pass over the data returning the pivot.

Also, many contemporary data bases have a PIVOT clause that can be leveraged via pass through.

The Hadoop Mania post "TRANSPOSE/PIVOT a Table in Hive" shows the use of collect_list and map in a similar wallpapery manner:

select b.id, b.code, concat_ws('',b.p) as p, concat_ws('',b.q) as q, concat_ws('',b.r) as r, concat_ws('',b.t) as t from
 (select id, code,
 collect_list(a.group_map['p']) as p,
 collect_list(a.group_map['q']) as q,
 collect_list(a.group_map['r']) as r,
 collect_list(a.group_map['t']) as t
 from ( select
  id, code,
  map(key,value) as group_map
  from test_sample
 ) a group by a.id, a.code) b;

SAS Hive SQL (Hadoop) version of Proc Transpose?

2 Answers