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 | 2015 |
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.
Minor typo there - the option should be NOTSORTED .
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;
Minor typo there - the option should be NOTSORTED .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.