topic Re: Transpose Query in New SAS User

Transpose Query

sanjaymane7 — Tue, 22 Dec 2020 10:23:37 GMT

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

Re: Transpose Query

andreas_lds — Tue, 22 Dec 2020 11:07:16 GMT

Why do want to change to a less useful format?

Are there always three observations for each policy_number?

Re: Transpose Query

GertNissen — Tue, 22 Dec 2020 11:56:45 GMT

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.

Re: Transpose Query

Kurt_Bremser — Tue, 22 Dec 2020 11:48:46 GMT

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.

Re: Transpose Query

Ksharp — Tue, 22 Dec 2020 13:04:56 GMT

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;