topic Re: Data Set Manipulation in SAS Programming

Data Set Manipulation

Dsrountree — Thu, 07 Aug 2014 19:56:17 GMT

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. Most people will pay the standard monthly Part B premium in addition to their MA plan premium. '$3 400 out-of-pocket limit for Medicare-covered services.
X1234	Doctor and Hospital Choice	You must go to network doctors specialists and hospitals. 'Referral required for network specialists (for certain benefits).
X5678	Premium and Other Important Information	$25.00 monthly plan premium in addition to your monthly Medicare Part B premium. 'Most people will pay the standard monthly Part B premium in addition to their MA plan premium. 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. '$3 400 out-of-pocket limit for Medicare-covered services. '$5 100 out-of-pocket limit for Medicare-covered services.
X5678	Doctor and Hospital Choice	No referral required for network doctors specialists and hospitals. 'You can go to doctors specialists and hospitals in or out of the network. It will cost more to get out of network benefits.

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. Most people will pay the standard monthly Part B premium in addition to their MA plan premium. '$3 400 out-of-pocket limit for Medicare-covered services.	$25.00 monthly plan premium in addition to your monthly Medicare Part B premium. 'Most people will pay the standard monthly Part B premium in addition to their MA plan premium. 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. '$3 400 out-of-pocket limit for Medicare-covered services. '$5 100 out-of-pocket limit for Medicare-covered services.
Doctor and Hospital Choice	You must go to network doctors specialists and hospitals. 'Referral required for network specialists (for certain benefits).	No referral required for network doctors specialists and hospitals. 'You can go to doctors specialists and hospitals in or out of the network. It will cost more to get out of network benefits.

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

Re: Data Set Manipulation

data_null__ — Thu, 07 Aug 2014 21:00:06 GMT

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.

filename FT15F001 templrecl=512; 
data hipa;
   infile FT15F001 dsddlm='|'; 
   length Contract_ID $5 CategoryDescription $64 CategoryCode 8 Benefit $256; 
   input (_all_)(:);
   parmcards4; 
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.
;;;;
;;;;

*'; 
data hipa;
   set hipa;
   by contract_id categoryCode;
   if first.categoryCode then indx=0; 
   indx+1; 
   run; 
procsortdata=hipa;
   by categoryCode indx Contract_ID;
   run; 
proctransposedata=hipa out=hippo;
   by CategoryCode CategoryDescription indx;
   var Benefit;
   id Contract_ID;
   run; 
ods tagsets.ExcelXP file='~/hippo.xml'; 
procreportdata=hippo spanrowsnowd; 
   columns CategoryCode CategoryDescription x:;
   Define categoryCode / orderorder=internal noprint; 
   define CategoryDescription / order; 
   define x: / display; 
   computeafter categorycode;
      line' '; 
      endcomp; 
   run; 
ods tagsets.excelxp close;

Re: Data Set Manipulation

Cynthia_sas — Fri, 08 Aug 2014 02:47:12 GMT

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;

Re: Data Set Manipulation

Dsrountree — Mon, 11 Aug 2014 12:26:09 GMT

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

Re: Data Set Manipulation

Dsrountree — Tue, 12 Aug 2014 19:40:18 GMT

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;

Re: Data Set Manipulation

data_null__ — Tue, 12 Aug 2014 20:26:16 GMT

define R: / display;

Re: Data Set Manipulation

data_null__ — Tue, 12 Aug 2014 20:53:47 GMT

Better, use PROC TRANSPOSE option to give the new variables a known PREFIX. See statement that use "ID_".

filename FT15F001 temp lrecl=512; 
data hipa;
   infile FT15F001 dsd dlm='|'; 
   length Contract_ID $5 CategoryDescription $64 CategoryCode 8 Benefit $256; 
   input (_all_)(:);
   parmcards4; 
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).
R5678|Premium and Other Important Information|1|$25.00 monthly plan premium in addition to your monthly Medicare Part B premium.
R5678|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
R5678|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.
;;;;
;;;;

*'; 
proc sort data=hipa;
   by contract_id categorycode;
   run; 
data hipa;
   set hipa;
   by contract_id categoryCode;
   if first.categoryCode then indx=0; 
   indx+1; 
   run; 
proc sort data=hipa;
   by categoryCode indx Contract_ID;
   run; 
proc transpose data=hipa out=hippo prefix=ID_;
   by CategoryCode CategoryDescription indx;
   var Benefit;
   id Contract_ID;

    idlabel Contract_ID;
   run; 
ods tagsets.ExcelXP file='~/hippo.xml'; 
proc report data=hippo spanrows nowd; 
   columns CategoryCode CategoryDescription ID_:;
   Define categoryCode / order order=internal noprint; 
   define CategoryDescription / order; 
   define ID_: / display; 
   compute after categorycode;
      line ' '; 
      endcomp; 
   run; 
ods tagsets.excelxp close;

Message was edited by: data _null_

Re: Data Set Manipulation

Dsrountree — Wed, 13 Aug 2014 18:04:05 GMT

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:

PLANNAME - DATA SET 1 - I've created a mock of the this data set below
STAR RATING - DATA SET 2 - I've created a mock of the this data set below
ENROLLMENT - DATA SET 3 - I've created a mock of the this data set below

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

;;;;

Re: Data Set Manipulation

data_null__ — Wed, 13 Aug 2014 18:35:55 GMT

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.

filename FT15F001 temp lrecl=512; 
data hipa;
   infile FT15F001 dsd dlm='|'; 
   length Contract_ID $5 CategoryDescription $64 CategoryCode 8 Benefit $256; 
   input (_all_)(:);
   parmcards4; 
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).
R5678|Premium and Other Important Information|1|$25.00 monthly plan premium in addition to your monthly Medicare Part B premium.
R5678|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
R5678|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.
;;;;
;;;;

*'; 
data set1 ;
   input (CONTRACT_ID PLAN_NAME) (:$20.);
   cards; 
R5678 PLAN_B
X12345 PLAN_A
X5678 PLAN_B
;;;;
 
data set2 ;
   input (CONTRACT_ID ENROLLMENT) (:$20. :F12.);
   cards; 
R5678 112000
X12345 111000
X5678 112000
;;;;
 
data set3 ;
   input (CONTRACT_ID STAR_RATING) (:$20.);
   cards; 
R5678 3.5 
X12345 4
X5678 5
;;;;
proc sort data=hipa;
   by contract_id categorycode;
   run; 
data hipa;
   merge hipa set1-set3;
   by contract_id;
   run; 
data hipa;
   set hipa;
   by contract_id categoryCode;
   if first.categoryCode then indx=0; 
   indx+1; 
   length idlabel $128; 
   idlabel = catx('~',plan_name,vvalue(Enrollment),star_rating,contract_id);
   format enrollment comma12.; 
   run; 
proc sort data=hipa;
   by categoryCode indx Contract_ID;
   run; 
proc transpose data=hipa out=hippo prefix=ID_;
   by CategoryCode CategoryDescription indx;
   var Benefit;
   id Contract_ID;
   idlabel idlabel;
   run; 
ods tagsets.ExcelXP file='~/hippo.xml'; 
proc report data=hippo spanrows nowd split='~'; 
   columns CategoryCode CategoryDescription ID_:;
   Define categoryCode / order order=internal noprint; 
   define CategoryDescription / order 'Plan Name~Enrollment~Star Rating~Category Description'; 
   define ID_: / display; 
   compute after categorycode;
      line ' '; 
      endcomp; 
   run; 
ods tagsets.excelxp close;

Re: Data Set Manipulation

Dsrountree — Thu, 21 Aug 2014 18:57:37 GMT

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
;;;;

Re: Data Set Manipulation

data_null__ — Thu, 21 Aug 2014 19:23:07 GMT

The problem is in the data step for HIPA where contract_id is defined LENGTH $5 should be $20.

filename FT15F001 temp lrecl=512; 
data hipa;
   infile FT15F001 dsd dlm='|'; 
   length Contract_ID $20 CategoryDescription $64 CategoryCode 8 Benefit $256; 
   input (_all_)(:);
   parmcards4; 
X12345-145-01|Premium and Other Important Information|1|$0 monthly plan premium in addition to your monthly Medicare Part B premium.
X12345-145-01|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
X12345-145-01|Premium and Other Important Information|1|$3 400 out-of-pocket limit for Medicare-covered services.
X12345-145-01|Doctor and Hospital Choice|2|You must go to network doctors specialists and hospitals.
X12345-145-01|Doctor and Hospital Choice|2|Referral required for network specialists (for certain benefits).
R5678-012-01|Premium and Other Important Information|1|$25.00 monthly plan premium in addition to your monthly Medicare Part B premium.
R5678-012-01|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
R5678-012-01|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-888-00|Premium and Other Important Information|1|$3 400 out-of-pocket limit for Medicare-covered services.
X5678-888-00|Premium and Other Important Information|1|$5 100 out-of-pocket limit for Medicare-covered services.
X5678-888-00|Doctor and Hospital Choice|2|No referral required for network doctors specialists and hospitals.
X5678-888-00|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.
;;;;
;;;;

*'; 
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-888-00 112000
;;;;

data set3 ;
   input (CONTRACT_ID STAR_RATING) (:$20.);
   cards; 
R5678-012-01 3.5 
X12345-145-01 4
X5678-888-00 5
;;;;

proc sort data=hipa;
   by contract_id categorycode;
   run; 
data hipa;
   merge hipa set1-set3;
   by contract_id;
   run; 
data hipa;
   set hipa;
   by contract_id categoryCode;
   if first.categoryCode then indx=0; 
   indx+1; 
   length idlabel $128; 
   idlabel = catx('~',plan_name,vvalue(Enrollment),star_rating,contract_id);
   format enrollment comma12.; 
   run; 
proc sort data=hipa;
   by categoryCode indx Contract_ID;
   run; 
proc transpose data=hipa out=hippo prefix=ID_;
   by CategoryCode CategoryDescription indx;
   var Benefit;
   id Contract_ID;
   idlabel idlabel;
   run; 
ods tagsets.ExcelXP file='~/hippo.xml'; 
proc report data=hippo spanrows nowd split='~'; 
   columns CategoryCode CategoryDescription ID_:;
   Define categoryCode / order order=internal noprint; 
   define CategoryDescription / order 'Plan Name~Enrollment~Star Rating~Category Description'; 
   define ID_: / display; 
   compute after categorycode;
      line ' '; 
      endcomp; 
   run; 
ods tagsets.excelxp close;