Transpose Query

[sanjaymane7]

I want to transpose below table –

Data set - have

Month	Policy_Number	IRDA_Amt	OpsRefNo
Nov-2020	XYZ	1000	20201005-M
Nov-2020	XYZ	500	20201015-A
Nov-2020	XYZ	100	20201222-A

Output (Want) -

Policy_Number	IRDA_Amt1	RefNo1	IRDA_Amt2	RefNo2	IRDA_Amt3	RefNo3
XYZ	1000	20201005-M	500	20201015-A	100	20201222-A

My Code –

proc transpose data=have out=want (Drop=_Name_ _LABEL_) prefix=irdaamt prefix=refno;

var IRDA_Amt OpsRefNo;

by Policy_Number;

run;

I am not getting output in desire amount. Please suggest

[andreas_lds]

Why do want to change to a less useful format?

Are there always three observations for each policy_number?

[GertNissen · Accepted solution]

Hi @sanjaymane7 

Here are two ideas, that might get you started and one solution

0) Sample data

data Have;
  input Month $ Policy_Number $ IRDA_Amt OpsRefNo $10.;
  datalines;
Nov-2020 XYZ 1000 20201005-M
Nov-2020 XYZ 500 20201015-A
Nov-2020 XYZ 100 20201222-A
Nov-2020 ZXY 1200 20201005-M
Nov-2020 ZXY 520 20201015-A
Nov-2020 ZXY 120 20201222-A
Dec-2020 XYZ 1300 20201005-M
Dec-2020 XYZ 530 20201015-A
Dec-2020 XYZ 130 20201222-A
;
run;

1) Two Transposes

proc transpose 
  data=have 
  out=IRDA_Amt (Drop=_Name_) prefix=IRDA_Amt ;
  var IRDA_Amt ;
  by month Policy_Number;
run;

proc transpose 
  data=have 
  out=OpsRefNo (Drop=_Name_) prefix=OpsRefNo ;
  var OpsRefNo ;
  by month Policy_Number;
run;

data want;
  merge IRDA_Amt
        OpsRefNo;
  by month Policy_Number;
run;

2) Using Arrays

proc sort data=have;
  by month Policy_Number;
run;

data want(drop=_:);
  array IRDA_Amt(3); retain IRDA_Amt:;
  array OpsRefNo(3); retain OpsRefNo:;
  set have(rename=(IRDA_Amt=_IRDA_Amt OpsRefNo=_OpsRefNo));
  by month Policy_Number;
  if first.Policy_Number then _n=0;
  _n+1;
   IRDA_Amt(_n) = _IRDA_Amt; 
   OpsRefNo(_n) = _OpsRefNo; 
  if last.Policy_Number then output;
run;

3) Suggested Solution

proc sort data=have;
  by month Policy_Number;
run;

data _have;
  set have;
  by month Policy_Number;
  if first.Policy_Number then n=1;
  else n+1;
run;

proc transpose 
  data=_have
  out=_temp;
  var OpsRefNo IRDA_Amt;
  by month Policy_Number n;
run;

proc transpose 
  data=_temp 
  out=want;
  var col1;
  by month Policy_Number ;
  id _name_  n;
run;

There are many ways to solve this (different programming styles), so the above suggestions are only small snippets or inspirational code - you will need to adapt the code and test the output.

[Kurt_Bremser]

If this is to be a report, consider PROC REPORT:

data have;
input Month $ Policy_Number $ IRDA_Amt OpsRefNo :$10.;
datalines;
Nov-2020 XYZ 1000 20201005-M
Nov-2020 XYZ 500 20201015-A
Nov-2020 XYZ 100 20201222-A
;

data have_x;
set have;
by policy_number;
if first.policy_number
then count = 1;
else count + 1;
run;

proc report data=have_x;
column policy_number count,(irda_amt opsrefno) n;
define policy_number / group;
define irda_amt / analysis sum;
define opsrefno / display;
define count / "" across;
define n / noprint;
run;

Wide datasets are needed for a very limited range of analysis (regression), and mostly useless for anything else.

[Ksharp]

data Have;
  input Month $ Policy_Number $ IRDA_Amt OpsRefNo $10.;
  datalines;
Nov-2020 XYZ 1000 20201005-M
Nov-2020 XYZ 500 20201015-A
Nov-2020 XYZ 100 20201222-A
;
run;
data have;
 set have;
 by Policy_Number;
 if first.Policy_Number  then n=0;
 n+1;
run;
proc sql noprint nowarn;
select distinct catt('have(where=(n=',n,' ) rename=(IRDA_Amt=IRDA_Amt_',n,' OpsRefNo=OpsRefNo_',n,'))') 
 into : merge separated by ' '
  from have
   order by n;
quit;
data want;
 merge &merge ;
 by Policy_Number;
 drop Month n;
run;