topic Re: Rows into columns in SAS Programming

Rows into columns

chithra — Tue, 28 Apr 2020 10:15:32 GMT

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

Re: Rows into columns

PeterClemmensen — Tue, 28 Apr 2020 10:33:14 GMT

Is Month numeric or character?

Re: Rows into columns

chithra — Tue, 28 Apr 2020 10:34:56 GMT

Character

Re: Rows into columns

Jagadishkatam — Tue, 28 Apr 2020 10:36:42 GMT

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;

Re: Rows into columns

chithra — Tue, 28 Apr 2020 10:44:20 GMT

Getting an error like bwlow:

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

Re: Rows into columns

Jagadishkatam — Tue, 28 Apr 2020 10:51:31 GMT

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;

Re: Rows into columns

chithra — Tue, 28 Apr 2020 10:55:23 GMT

Yes there are

Re: Rows into columns

Jagadishkatam — Tue, 28 Apr 2020 10:57:48 GMT

Could you please post that data

Re: Rows into columns

Kurt_Bremser — Tue, 28 Apr 2020 10:59:47 GMT

@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

Re: Rows into columns

chithra — Tue, 28 Apr 2020 11:04:39 GMT

you mean only for these 3 columns right?

irtespective of other column?

Re: Rows into columns

chithra — Tue, 28 Apr 2020 11:07:28 GMT

It is worked..Thanks

Re: Rows into columns

ballardw — Tue, 28 Apr 2020 15:07:20 GMT

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.