Sorry I couldn't create a mock data set - it would only complicate things further.
I need everyone to see a real sample of the data (of course with actual contract_id's removed) HIPPA
DATASET A:
Contract_ID | CategoryDescription | CategoryCode | Benefit |
X1234 | Premium and Other Important Information | 1 | $0 monthly plan premium in addition to your monthly Medicare Part B premium. |
X1234 | Premium and Other Important Information | 1 | Most people will pay the standard monthly Part B premium in addition to their MA plan premium. |
X1234 | Premium and Other Important Information | 1 | $3 400 out-of-pocket limit for Medicare-covered services. |
X1234 | Doctor and Hospital Choice | 2 | You must go to network doctors specialists and hospitals. |
X1234 | Doctor and Hospital Choice | 2 | Referral required for network specialists (for certain benefits). |
X5678 | Premium and Other Important Information | 1 | $25.00 monthly plan premium in addition to your monthly Medicare Part B premium. |
X5678 | Premium and Other Important Information | 1 | Most people will pay the standard monthly Part B premium in addition to their MA plan premium. |
X5678 | Premium and Other Important Information | 1 | Some physicians providers and suppliers that are out of a plan's network (i.e. out-of-network) accept "assignment" from Medicare and will only charge up to a Medicare-approved amount. |
X5678 | Premium and Other Important Information | 1 | $3 400 out-of-pocket limit for Medicare-covered services. |
X5678 | Premium and Other Important Information | 1 | $5 100 out-of-pocket limit for Medicare-covered services. |
X5678 | Doctor and Hospital Choice | 2 | No referral required for network doctors specialists and hospitals. |
X5678 | Doctor and Hospital Choice | 2 | You can go to doctors specialists and hospitals in or out of the network. It will cost more to get out of network benefits. |
Needs to be converted to the following:
· CategoryCode was removed
· CategoryDescription was consolidated for each Contract_ID
· Benefit was consolidated under CategoryDescription (leaving a space between each row)
Contract_ID | CategoryDescription | Benefit |
X1234 | Premium and Other Important Information | $0 monthly plan premium in addition to your monthly Medicare Part B premium. |
X1234 | Doctor and Hospital Choice | You must go to network doctors specialists and hospitals. |
X5678 | Premium and Other Important Information | $25.00 monthly plan premium in addition to your monthly Medicare Part B premium. |
X5678 | Doctor and Hospital Choice | No referral required for network doctors specialists and hospitals. |
FINAL STEP:
· Transpose and move Contract_ID to header and Description to left of page
o May contain more than 2 Contract_ID’s per comparison
· CategoryDescription is ordered based on numeric and varies from 1 to 40
o May contain more than 2 CategoryDescription/CategoryCode
CategoryDescription | X1234 Benefits | X5678 Benefits |
Premium and Other Important Information | $0 monthly plan premium in addition to your monthly Medicare Part B premium. | $25.00 monthly plan premium in addition to your monthly Medicare Part B premium. |
Doctor and Hospital Choice | You must go to network doctors specialists and hospitals. | No referral required for network doctors specialists and hospitals. |
Desired output is to take a difficult .DB and manipulate into an Excel based format that will allow the end user to view each contract
and do a side by side comparison of each Benefit and how they apply to each CategoryDescription.
Think of doing a side by side by side by side of various vehicles looking at the key components to assist in your buying decision on
I think you will get into trouble if you try to combine the individual benefits into one long variable. You can get almost what you want this way and it looks mostly the same to me. I imagine could do the PROC REPORT without the transpose but this works too. You should be able to get the EXCEL to look like you want, with a few options on the ODS statement, should look sort of like the RTF output below.
X1234|Premium and Other Important Information|1|$0 monthly plan premium in addition to your monthly Medicare Part B premium.
X1234|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
X1234|Premium and Other Important Information|1|$3 400 out-of-pocket limit for Medicare-covered services.
X1234|Doctor and Hospital Choice|2|You must go to network doctors specialists and hospitals.
X1234|Doctor and Hospital Choice|2|Referral required for network specialists (for certain benefits).
X5678|Premium and Other Important Information|1|$25.00 monthly plan premium in addition to your monthly Medicare Part B premium.
X5678|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
X5678|Premium and Other Important Information|1|Some physicians providers and suppliers that are out of a plan's network (i.e. out-of-network) accept "assignment" from Medicare and will only charge up to a Medicare-approved amount.
X5678|Premium and Other Important Information|1|$3 400 out-of-pocket limit for Medicare-covered services.
X5678|Premium and Other Important Information|1|$5 100 out-of-pocket limit for Medicare-covered services.
X5678|Doctor and Hospital Choice|2|No referral required for network doctors specialists and hospitals.
X5678|Doctor and Hospital Choice|2|You can go to doctors specialists and hospitals in or out of the network. It will cost more to get out of network benefits.
;;;;
;;;;I think you will get into trouble if you try to combine the individual benefits into one long variable. You can get almost what you want this way and it looks mostly the same to me. I imagine could do the PROC REPORT without the transpose but this works too. You should be able to get the EXCEL to look like you want, with a few options on the ODS statement, should look sort of like the RTF output below.
X1234|Premium and Other Important Information|1|$0 monthly plan premium in addition to your monthly Medicare Part B premium.
X1234|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
X1234|Premium and Other Important Information|1|$3 400 out-of-pocket limit for Medicare-covered services.
X1234|Doctor and Hospital Choice|2|You must go to network doctors specialists and hospitals.
X1234|Doctor and Hospital Choice|2|Referral required for network specialists (for certain benefits).
X5678|Premium and Other Important Information|1|$25.00 monthly plan premium in addition to your monthly Medicare Part B premium.
X5678|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
X5678|Premium and Other Important Information|1|Some physicians providers and suppliers that are out of a plan's network (i.e. out-of-network) accept "assignment" from Medicare and will only charge up to a Medicare-approved amount.
X5678|Premium and Other Important Information|1|$3 400 out-of-pocket limit for Medicare-covered services.
X5678|Premium and Other Important Information|1|$5 100 out-of-pocket limit for Medicare-covered services.
X5678|Doctor and Hospital Choice|2|No referral required for network doctors specialists and hospitals.
X5678|Doctor and Hospital Choice|2|You can go to doctors specialists and hospitals in or out of the network. It will cost more to get out of network benefits.
;;;;
;;;;Hi:
Pretty much, my output is the same as the above output. I did it using ACROSS and not doing the TRANSPOSE step. Just made a few fake variables to help with the grouping.
Cynthia
options linesize=256;
data hipa;
infile datalines4 dsd dlm=',' ;
length Contract_ID $5 CategoryDescription $64 CategoryCode 8 Benefit $256;
input (_all_)(:);
roword = categorycode;
datalines4;
X1234,"Premium and Other Important Information",1,"$0 monthly plan premium in addition to your monthly Medicare Part B premium."
X1234,"Premium and Other Important Information",1,"Most people will pay the standard monthly Part B premium in addition to their MA plan premium."
X1234,"Premium and Other Important Information",1,"$3 400 out-of-pocket limit for Medicare-covered services."
X1234,"Doctor and Hospital Choice",2,"You must go to network doctors specialists and hospitals."
X1234,"Doctor and Hospital Choice",2,"Referral required for network specialists (for certain benefits)."
X5678,"Premium and Other Important Information",1,"$25.00 monthly plan premium in addition to your monthly Medicare Part B premium."
X5678,"Premium and Other Important Information",1,"Most people will pay the standard monthly Part B premium in addition to their MA plan premium"
X5678,"Premium and Other Important Information",1,"Some physicians providers and suppliers that are out of a plan's network (i.e. out-of-network) accept 'assignment' from Medicare and will only charge up to a Medicare-approved amount."
X5678,"Premium and Other Important Information",1,"$3 400 out-of-pocket limit for Medicare-covered services."
X5678,"Premium and Other Important Information",1,"$5 100 out-of-pocket limit for Medicare-covered services."
X5678,"Doctor and Hospital Choice",2,"No referral required for network doctors specialists and hospitals."
X5678,"Doctor and Hospital Choice",2,"You can go to doctors specialists and hospitals in or out of the network. It will cost more to get out of network benefits."
;;;;
run;
proc sort data=hipa;
by roword contract_id categorydescription categorycode;
run;
data final;
set hipa;
by roword contract_id categorydescription categorycode;
if first.contract_id then cntr = 0;
cntr+1;
run;
proc sort data = final;
by contract_id roword cntr;
run;
ods listing close;
ods html file='c:\temp\rotate.html';
proc report data=final nowd spanrows;
column roword categorydescription cntr contract_id,(Benefit) n;
define roword / group noprint ;
define categorydescription / group order=data;
define cntr / group noprint;
define contract_id / across ' ';
define benefit / display;
define n / 'count' noprint /* used for collapsing of character values*/;
compute after roword;
line ' ';
endcomp;
run;
ods html close;
This is great - I enjoy new challenges and my SAS skills are still developing so this was very useful.
Working in my organization I am constantly asked to change the layout of data, so seeing how two strong programmers handled it was great.
Thanks again - Love this forrum
If the X: value has another variable that starts with an "R" how would you modify the PROC REPORT to handle that condition?
proc report data=hippo spanrows nowd;
columns CategoryCode CategoryDescription x:;
Define categoryCode / order order=internal noprint;
define CategoryDescription / order;
define x: / display;
compute after categorycode;
line ' ';
endcomp;
run;
R:
define R: / display;
Better, use PROC TRANSPOSE option to give the new variables a known PREFIX. See statement that use "ID_".
Message was edited by: data _null_
FYI:
Management loved the first draft (Thanks data_null_; & Cynthia@SAS) , but I need to make a few additional changes to the output...
Suppose I had three other data sets that needed to be added to the header (SEE GREEN) portion only of the PROC REPORT:
Can they be added to the top of Report?
I call this trial by fire - Normally this process is done manually and takes 2 weeks to complete with over 100 side by side plan comparisons...
For simplicity the mock data is all CHAR - the numbers will be NUMERIC or CHAR in real data pull:
data set1 ;
input (CONTRACT_ID PLAN_NAME) (:$20.);
cards;
X12345 PLAN_A
X5678 PLAN_B
;;;;
data set2 ;
input (CONTRACT_ID ENROLLMENT) (:$20.);
cards;
X12345 111000
X5678 112000
;;;;
data set3 ;
input (CONTRACT_ID STAR_RATING) (:$20.);
cards;
X12345 4
X5678 5
;;;;
For the TRANSPOSE you can just create a LABEL that contains the data from the other 3 variables. You don't get separate cells as in you example but perhaps that will not be an issue.
How do I get the full number to display in the category Description?
See longer numbers below:
data set1 ;
input (CONTRACT_ID PLAN_NAME) (:$20.);
cards;
R5678-012-01 PLAN_B
X12345-145-01 PLAN_A
X5678-888-00 PLAN_B
;;;;
data set2 ;
input (CONTRACT_ID ENROLLMENT) (:$20. :F12.);
cards;
R5678-012-01 112000
X12345-145-01 111000
X5678 112000
;;;;
data set3 ;
input (CONTRACT_ID STAR_RATING) (:$20.);
cards;
R5678-012-01 3.5
X12345-145-01 4
X5678-888-00 5
;;;;
The problem is in the data step for HIPA where contract_id is defined LENGTH $5 should be $20.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.