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
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.
Why do want to change to a less useful format?
Are there always three observations for each policy_number?
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.
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.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.