Pivot translation in SAS SQL, more simply?

Question

I’m trying pivot translation in SAS proc SQL, and I already got my desired dataset. But I’m seeking more simple program, without the duplicate program “max(case when NO = # then ### end) as ##”, or something better way. If you have a good one, please give me some advice.

data test;
input ID $ NO TIM $;
cards;
1   1   8:31
1   2   8:32
1   3   8:33
1   4   8:34
2   1   8:35
2   2   8:36
2   3   8:37
2   4   8:38
3   1   8:41
3   2   8:42
3   3   8:43
3   4   8:44
;
run;

proc sql;
 create table test2 as
 select ID,
        max(case when NO = 1 then TIM end) as A1,
        max(case when NO = 2 then TIM end) as B1,
        max(case when NO = 3 then TIM end) as C1,
        max(case when NO = 4 then TIM end) as D1
 from test group by ID;
quit;

/*
test2 : I want this dataset

ID  A1      B1      C1      D1
1   8:31    8:32    8:33    8:34
2   8:35    8:36    8:37    8:38
3   8:41    8:42    8:43    8:44
*/

Agree with @Tom. Provided SQL is bad and dangerous solution. — crow16384, Nov 16 '20 at 20:20
SQL is an excellent solution. SAS's PROC SQL is beyond repair, though. — wildplasser, Nov 16 '20 at 21:23
@crow16384, why is provided SQL a *bad and dangerous solution*? — Parfait, Nov 16 '20 at 22:00
@Parfait, SAS SQL is excellent and powerful, but solution in the question is coming with some not good thinks: 1) case without default; 2) implicit conversion character data to numeric with max - dangerous, better to use input function; 3) provided SQL is not flexible (hardcoded) solution. PROC TRANSPOSE let you to avoid all of such issues and make code flexible. — crow16384, Nov 17 '20 at 04:51
@crow16384, understood your point. OP's attempt is known as [conditional aggregation](https://stackoverflow.com/q/20111418/1422451) which is the classic way in SQL to transpose rows to columns for a limited number of categories. As you note in SAS, it may not be the most optimal but *bad and dangerous* may confuse future readers. Some dialects like SAS carry SQL-plus solutions: SQL Server and Oracle with the `PIVOT` clause or Postgres with `crosstab` and MS Access with `TRANSFORM`. — Parfait, Nov 17 '20 at 16:08

Tom · Accepted Answer · 2020-11-16T22:38:57.233

Looks like a simple transpose step. You can use the ID statement to tell it to use the value of one or more variables to construct the variable names. You can use the PREFIX= option to set a base name for the variables. So this code will make variables named VAR1, VAR2, VAR3 and VAR4 from your example data.

proc transpose data=test out=want(drop=_name_) prefix=VAR ;
  by id;
  id no;
  var tim;
run;

If you want the variables to be named the way you have shown instead then you will need to build some way to transform 1 into 'A1' and 2 into 'B1' etc. For example you could define a format that maps 1 to 'A1'.

proc format ;
  value abc 1='A1' 2='B1' 3='C1' 4='D1';
run;

proc transpose data=test out=want(drop=_name_);
  by id;
  id no;
  var tim;
  format no abc.;
run;

Thank you for your answer! it would be very helpful. And everyone, thank you for useful advice and discussion. — 51sep, Nov 18 '20 at 19:45

Pivot translation in SAS SQL, more simply?

1 Answers1