What is the easiest way to parse a data set and create multiple data sets based on a given value?   Example:  Let’s use telephone numbers and look for all values found in PHONENUMBER beginning with 631 and put them in data set A 516 and put them in data set B 212 and put them in data set C   Data: 631 555 5555 631 666 6666 516 999 9999 516 888 8888 212 444 4444 212 444 5555   Problem:  You don’t know the first 3 values – If I did – I would write a substring/do loop to read the first 3 of the field “PHONENUMBER” and tell it to read through the table and when it finds a matching value “substr (phonenumber,1,3) write it out to the associated table.   What function would you use to search the field PHONENUMBER and look for all like values from a specific position and place those values in a separate data set?   This is without defining the values to look for since the list of area codes is quite large.   All help is greatly appreciated – I haven’t been in the SAS world for over 2 years so I’m a little rusty – Thanks in advance     ... View more

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. ... View more

Prior to introducing the macro to this part of the code – each section would create an XML file based on the sheet_name=’     ‘; Now that I’ve created a macro that reads each data set and performs the function for all eight datasets the naming isn’t working. Perhaps it’s something simple missing between the ‘   ‘ – ex: ‘&&tb4’ right now it writes each tab for the output data as tb4, tb4 2, tb4 3, …… incrementing by 1 after the tb4. I’m not sure – New Jack SAS Guy ODS TAGSETS.EXCELXP file='/windows/Infodata/DRountree/CAB/2014_2015_Competitive_Analysis_HMO_NYC.xml'; *** Define macro3 ***; %macro macro3(tb4); ODS TAGSETS.EXCELXP options(sheet_interval='table' suppress_bylines='yes' sheet_name= '&tb4'); PROC REPORT DATA = &tb4  spanrows nowd split='~'; columns CategoryCode CategoryDescription ID_:; Define CategoryCode / order order=internal noprint; define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP'; define ID_: / display; compute after CategoryCode; line ' '; endcomp; run; %mend macro3; *** Run macro3 ***; %macro3(Aetna_14); %macro3(Empire_BCBS_14); %macro3(Healthfirst_14); %macro3(UH_AARP_14); %macro3(Aetna_15); %macro3(Empire_BCBS_15); %macro3(Healthfirst_15); %macro3(UH_AARP_15); ODS _ALL_ CLOSE; ... View more