BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chithra
Quartz | Level 8

Hi Team,

 

I have a dataset like below:

MonthPolicyMPindprodPolicies
Jan-20BBABSL  1284
Jan-20BBBarclays  779
Feb-20BBABSL  1279
Feb-20BBBarclays  774
Feb-20BBLloyds FL169
Mar-20BBBarclays  764
Mar-20BBLloyds FL166

 

and the desired output is like :

 

PolicyMPindprodJan-20Feb-20Mar-20
BBABSL  12841279 
BBBarclays  779774764
BBLloyds FL 169166

 

Could you please help?

 

Thanks,

Chithra

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@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

View solution in original post

11 REPLIES 11
PeterClemmensen
Tourmaline | Level 20

Is Month numeric or character?

chithra
Quartz | Level 8

Character

Jagadishkatam
Amethyst | Level 16

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;
 
Thanks,
Jag
chithra
Quartz | Level 8

Getting an error like bwlow:

 

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

Jagadishkatam
Amethyst | Level 16
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;
Thanks,
Jag
chithra
Quartz | Level 8

Yes there are

Jagadishkatam
Amethyst | Level 16
Could you please post that data
Thanks,
Jag
chithra
Quartz | Level 8

you mean only for these 3 columns right?

irtespective of other column?

Kurt_Bremser
Super User

@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
Quartz | Level 8

It is worked..Thanks

ballardw
Super User

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.

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 870 views
  • 0 likes
  • 5 in conversation