DATA Step, Macro, Functions and more

Proc Transpose - Removing dupes

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Proc Transpose - Removing dupes

The following data was transposed:

PBP_NumberCategoryCodeCategoryDescriptionBenefitsentences_sort_orderStar_RatingEnrollmentindxidlabelTaborder
HXXXXX-019-07Dental ServicesCleaning (for up to 1 every six months): You pay nothing6233.5 out of 5 stars42442015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX19-021
HXXXXX-019-07Dental ServicesCleaning (for up to 1 every six months): You pay nothing6233.5 out of 5 stars42442015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-07Dental ServicesCleaning (for up to 1 every six months): You pay nothing6233.5 out of 5 stars1268232015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-07Dental ServicesCleaning (for up to 1 every six months): You pay nothing6233.5 out of 5 stars1268232015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-07Dental ServicesDental x-ray(s) (for up to 1 every six months): You pay nothing6563.5 out of 5 stars42462015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-07Dental ServicesDental x-ray(s) (for up to 1 every six months): You pay nothing6563.5 out of 5 stars42462015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-07Dental ServicesDental x-ray(s) (for up to 1 every six months): You pay nothing6563.5 out of 5 stars1268252015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-07Dental ServicesDental x-ray(s) (for up to 1 every six months): You pay nothing6563.5 out of 5 stars1268252015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-07Dental ServicesOral exam (for up to 1 every six months): You pay nothing7223.5 out of 5 stars42482015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-07Dental ServicesOral exam (for up to 1 every six months): You pay nothing7223.5 out of 5 stars42482015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-07Dental ServicesOral exam (for up to 1 every six months): You pay nothing7223.5 out of 5 stars1268272015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-07Dental ServicesOral exam (for up to 1 every six months): You pay nothing7223.5 out of 5 stars1268272015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-07Dental ServicesPreventive dental services:6223.5 out of 5 stars42412015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-07Dental ServicesPreventive dental services:6223.5 out of 5 stars42412015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-07Dental ServicesPreventive dental services:6223.5 out of 5 stars1268222015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-07Dental ServicesPreventive dental services:6223.5 out of 5 stars1268222015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-08Diabetes Supplies and ServicesDiabetes monitoring supplies: You pay nothing8023.5 out of 5 stars42412015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-08Diabetes Supplies and ServicesDiabetes monitoring supplies: You pay nothing8023.5 out of 5 stars42412015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-08Diabetes Supplies and ServicesDiabetes monitoring supplies: You pay nothing8023.5 out of 5 stars1268222015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-08Diabetes Supplies and ServicesDiabetes monitoring supplies: You pay nothing8023.5 out of 5 stars1268222015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-08Diabetes Supplies and ServicesDiabetes self-management training: You pay nothing8343.5 out of 5 stars42442015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-08Diabetes Supplies and ServicesDiabetes self-management training: You pay nothing8343.5 out of 5 stars42442015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-08Diabetes Supplies and ServicesDiabetes self-management training: You pay nothing8343.5 out of 5 stars1268232015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-08Diabetes Supplies and ServicesDiabetes self-management training: You pay nothing8343.5 out of 5 stars1268232015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-08Diabetes Supplies and ServicesTherapeutic shoes or inserts: You pay nothing8663.5 out of 5 stars42462015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-08Diabetes Supplies and ServicesTherapeutic shoes or inserts: You pay nothing8663.5 out of 5 stars42462015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-011
HXXXXX-019-08Diabetes Supplies and ServicesTherapeutic shoes or inserts: You pay nothing8663.5 out of 5 stars1268252015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-021
HXXXXX-019-08Diabetes Supplies and ServicesTherapeutic shoes or inserts: You pay nothing8663.5 out of 5 stars1268252015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-011

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.


Accepted Solutions
Solution
‎10-02-2014 12:24 AM
Contributor dkb
Contributor
Posts: 53

Re: Proc Transpose - Removing dupes

Minor typo there - the option should be NOTSORTED .

View solution in original post


All Replies
Super User
Posts: 17,960

Re: Proc Transpose - Removing dupes

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;

Solution
‎10-02-2014 12:24 AM
Contributor dkb
Contributor
Posts: 53

Re: Proc Transpose - Removing dupes

Minor typo there - the option should be NOTSORTED .

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 440 views
  • 3 likes
  • 3 in conversation