Rows into columns

[chithra]

Hi Team,

I have a dataset like below:

Month	Policy	MP	ind	prod	Policies
Jan-20	BB	ABSL			1284
Jan-20	BB	Barclays			779
Feb-20	BB	ABSL			1279
Feb-20	BB	Barclays			774
Feb-20	BB	Lloyds		FL	169
Mar-20	BB	Barclays			764
Mar-20	BB	Lloyds		FL	166

and the desired output is like :

Policy	MP	ind	prod	Jan-20	Feb-20	Mar-20
BB	ABSL			1284	1279
BB	Barclays			779	774	764
BB	Lloyds		FL		169	166

Could you please help?

Thanks,

Chithra

[PeterClemmensen]

Is Month numeric or character?

[chithra]

Character

[Jagadishkatam]

please try the below code

proc sort data=have;
by policy mp ind prod;
run;

proc transpose data=have out=want;
by policy mp ind prod;
var policies;
id month;
run;
 

[chithra]

Getting an error like bwlow:

ERROR: The ID value "'Jan-20'n" occurs twice in the same BY group.

[Jagadishkatam]

Please check your data if there are duplicate records on the below by statement variables, there shouldnt be any duplicates on the below by variables in your dataset

by policy mp ind prod;

[chithra]

Yes there are

[Jagadishkatam]

Could you please post that data

[chithra]

you mean only for these 3 columns right?

irtespective of other column?

[Kurt_Bremser · Accepted solution]

---

@chithra wrote:

Getting an error like bwlow:

ERROR: The ID value "'Jan-20'n" occurs twice in the same BY group.

---

Not with the data you posted.

data have;
infile datalines dsd dlm='09'x truncover;
input Month :$6. Policy	:$2. MP :$10. ind prod :$2. Policies;
datalines;
Jan-20	BB	ABSL	 	 	1284
Jan-20	BB	Barclays	 	 	779
Feb-20	BB	ABSL	 	 	1279
Feb-20	BB	Barclays	 	 	774
Feb-20	BB	Lloyds	 	FL	169
Mar-20	BB	Barclays	 	 	764
Mar-20	BB	Lloyds	 	FL	166
;

proc sort data=have;
by policy mp ind prod;
run;

proc transpose data=have out=want;
by policy mp ind prod;
var policies;
id month;
run;

Log:

 73         data have;
 74         infile datalines dsd dlm='09'x truncover;
 75         input Month :$6. Policy:$2. MP :$10. ind prod :$2. Policies;
 76         datalines;
 
 NOTE: The data set WORK.HAVE has 7 observations and 6 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 84         ;
 85         
 86         proc sort data=have;
 87         by policy mp ind prod;
 88         run;
 
 NOTE: There were 7 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.HAVE has 7 observations and 6 variables.
 NOTE:  Verwendet wurde: PROZEDUR SORT - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 89         
 90         proc transpose data=have out=want;
 91         by policy mp ind prod;
 92         var policies;
 93         id month;
 94         run;
 
 NOTE: There were 7 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.WANT has 3 observations and 8 variables.
 NOTE:  Verwendet wurde: PROZEDUR TRANSPOSE - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.01 seconds

[chithra]

It is worked..Thanks

[ballardw]

Do you need a data set (for further analysis/modeling) or a report (people read these)?

BTW anything involving dates you may want to consider creating SAS date valued numeric instead of character. You can manipulate dates much easier, create groups by changing formats and they SORT properly.