Proc Transpose - Removing dupes

[Dsrountree]

The following data was transposed:

PBP_Number	CategoryCode	CategoryDescription	Benefit	sentences_sort_order	Star_Rating	Enrollment	indx	idlabel	Taborder
HXXXXX-019-0	7	Dental Services	Cleaning (for up to 1 every six months): You pay nothing	623	3.5 out of 5 stars	424	4	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX19-0	21
HXXXXX-019-0	7	Dental Services	Cleaning (for up to 1 every six months): You pay nothing	623	3.5 out of 5 stars	424	4	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Cleaning (for up to 1 every six months): You pay nothing	623	3.5 out of 5 stars	12682	3	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Cleaning (for up to 1 every six months): You pay nothing	623	3.5 out of 5 stars	12682	3	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Dental x-ray(s) (for up to 1 every six months): You pay nothing	656	3.5 out of 5 stars	424	6	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Dental x-ray(s) (for up to 1 every six months): You pay nothing	656	3.5 out of 5 stars	424	6	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Dental x-ray(s) (for up to 1 every six months): You pay nothing	656	3.5 out of 5 stars	12682	5	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Dental x-ray(s) (for up to 1 every six months): You pay nothing	656	3.5 out of 5 stars	12682	5	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Oral exam (for up to 1 every six months): You pay nothing	722	3.5 out of 5 stars	424	8	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Oral exam (for up to 1 every six months): You pay nothing	722	3.5 out of 5 stars	424	8	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Oral exam (for up to 1 every six months): You pay nothing	722	3.5 out of 5 stars	12682	7	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Oral exam (for up to 1 every six months): You pay nothing	722	3.5 out of 5 stars	12682	7	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Preventive dental services:	622	3.5 out of 5 stars	424	1	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Preventive dental services:	622	3.5 out of 5 stars	424	1	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Preventive dental services:	622	3.5 out of 5 stars	12682	2	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Preventive dental services:	622	3.5 out of 5 stars	12682	2	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing	802	3.5 out of 5 stars	424	1	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing	802	3.5 out of 5 stars	424	1	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing	802	3.5 out of 5 stars	12682	2	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing	802	3.5 out of 5 stars	12682	2	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes self-management training: You pay nothing	834	3.5 out of 5 stars	424	4	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes self-management training: You pay nothing	834	3.5 out of 5 stars	424	4	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes self-management training: You pay nothing	834	3.5 out of 5 stars	12682	3	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes self-management training: You pay nothing	834	3.5 out of 5 stars	12682	3	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Therapeutic shoes or inserts: You pay nothing	866	3.5 out of 5 stars	424	6	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Therapeutic shoes or inserts: You pay nothing	866	3.5 out of 5 stars	424	6	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Therapeutic shoes or inserts: You pay nothing	866	3.5 out of 5 stars	12682	5	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Therapeutic shoes or inserts: You pay nothing	866	3.5 out of 5 stars	12682	5	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11

This data contains duplicate values in the BENEFIT column after I’ve indexed the columns to make sure the data is sorted according to sentences_sort_order.

I need to remove the duplicates much like you do in Excel after the columns are created.  Other PBP_Numbers when transposed don’t always contain duplicates in the BENEFIT column.

PROC TRANSPOSE DATA = Benefits Plan_HXXXXX_15 OUT = Benefits Plan_HXXXXX_15 PREFIX = ID_ ;

BY CategoryCode CategoryDescription indx;

var Benefit;

id PBP_Number ;

idlabel idlabel;

RUN;

PROC REPORT DATA = Benefits Plan _HXXXXX_15 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Contract Year~Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

Contract Year Plan Name Current Enrollment Star Rating Contract Number/PBP	2015 Benefits Plan (HMO) 424 3.5 out of 5 stars HXXXXX-019-0
Dental Services	Preventive dental services:
	Preventive dental services:
	Cleaning (for up to 1 every six months): You pay nothing
	Cleaning (for up to 1 every six months): You pay nothing
	Dental x-ray(s) (for up to 1 every six months): You pay nothing
	Dental x-ray(s) (for up to 1 every six months): You pay nothing
	Oral exam (for up to 1 every six months): You pay nothing
	Oral exam (for up to 1 every six months): You pay nothing
Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing
	Diabetes monitoring supplies: You pay nothing
	Diabetes self-management training: You pay nothing
	Diabetes self-management training: You pay nothing
	Therapeutic shoes or inserts: You pay nothing
	Therapeutic shoes or inserts: You pay nothing

I know when I added the additional data IE: Star_Rating & Enrollment via a left outer join this created my dups.

The problem is I can't do an inner since I need to make sure all the value(s) found in the left table remain in my extract and only pull a value when it finds a matching PBP_Number.

[Reeza]

Do it in a simple data step using the unsorted option?

data want;

set have;

by var1 var2 var3 var4 unsorted;

if first.var4;

run;

[dkb · Accepted solution]

Minor typo there - the option should be NOTSORTED .