About Dsrountree

Dsrountree · ‎08-22-2014

You helped me with the first part of this (Proc Transpose & Proc Report) The problem is I can't use the output generated from Proc Report - since it's not in the same layout as the FINAL ODS excel file created. I trouble shooted by adding an OUT= to the Proc Report and this was obviously not the correct direction since the output contained_NAME_, etc and it's not in the same layout as the report or the XML file. Sorry to pull you into this mess - Enjoy your weekend PROC REPORT DATA=Empire_X3370 OUT=Empire_X3370 spanrows nowd split='~';

Dsrountree · ‎08-22-2014

I currently use this process to append multiple datasets to one excel document at any given location: the two data sets are exported and become two seperate tabs in the "&folder.CPSC_Enrollment__&dt..xls" %let pc = NYWSWXXXXXX; %let folder = C:\Users\drountree\; data _null_; dt=put( today( ),date9. ); call symput('dt',dt); run; proc export data = Summary_By_Org_HMO outfile = "&folder.CPSC_Enrollment__&dt..xls" dbms = excelcs replace; sheet = "Summary_By_Org_HMO"; SERVER = "&pc"; run; proc export data = Summary_By_Org_PPO outfile = "&folder.CPSC_Enrollment__&dt..xls" dbms = excelcs replace; sheet = "Summary_By_Org_PPO"; SERVER = "&pc"; run; Now this is where it gets fun. I understand fully the section above and I'm able to code multiple datasets meeting various requirements and have them output to various .XLS on different tabs based on a given criteria. MY PROBLEM: I need to take a newly learned process via data_null; (ODS tagsets subset PROC REPORT) and take the output Transposed file and export each one to the same xls as above based on a given condition. PROC TRANSPOSE DATA=Empire_X3370 OUT=Empire_X3370 PREFIX=ID_; SYSECHO "PROC TRANSPOSE DATA=ABRIDGED OUT=ABRIDGED PREFIX=ID_;"; BY CategoryCode CategoryDescription indx; var Benefit; id PBP_Number; idlabel idlabel; RUN; ods tagsets.ExcelXP file= '/windows/Infodata/DRountree/CAB/Empire_X3370.xml'; PROC REPORT DATA=Empire_X3370 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; ods tagsets.excelxp close; The above code creates one data set Empire_X3370 and exports a XML file to the specified location. I'm creating several different comparissions and need to export Blue_X5590 to the same file but in a different tab. Keep in mind if this wasn't a PROC REPORT ODS XML output then this would be easy and I could use the first set of code above: %let pc = NYWSWXXXXXX; %let folder = C:\Users\drountree\; data _null_; dt=put( today( ),date9. ); call symput('dt',dt); run; proc export data = Empire_X3370 outfile = "&folder.CPSC_Enrollment__&dt..xls" dbms = excelcs replace; sheet = "Empire_X3370"; SERVER = "&pc"; run; proc export data = Blue_X5590 outfile = "&folder.CPSC_Enrollment__&dt..xls" dbms = excelcs replace; sheet = "Blue_X5590"; SERVER = "&pc"; run; The problem is the output from the PROC REPORT...

Dsrountree · ‎08-21-2014

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

Dsrountree · ‎08-13-2014

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

Dsrountree · ‎08-12-2014

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;

Dsrountree · ‎08-11-2014

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

Dsrountree · ‎08-07-2014

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

Dsrountree · ‎07-15-2014

Thanks for all your help and input... I agree with the comment in regards to the layout but upper mgt wants it layed out the same way you would see specs on different model cars (top down). CAR A CAR B CAR C 32 MPG 29 MPG 40MPG 2 DOOR 2 DOOR 4 DOOR 300 HP 450 HP 250 HP During one of my many data pulls the data would never be presented in this fashion but when mgt is looking to compare several competitive plans this is the layout they request. In the past it was done manually by another analyst who couldn't transpose the data correctly. Again Thanks - I love the feedback I get - trust me it helps alot.

Dsrountree · ‎07-14-2014

Jaap, Can you please elaborate? Thanks...

Dsrountree · ‎07-14-2014

How do I transpose “WANT” to view as SAMPLE A below? Keep in mind the list of variables aren't just defined to: ID Crayon PayFine Fruit & ChargeTax. There are over 60 variables. DATA A: length name $ 30 codes $ 10 codevalues $50 ; input NAME CODES CODEVALUES ; cards; Crayon 01 Blue Crayon 02 Red Crayon 03 Yellow PayFine . . Fruit 01 Apple Fruit 02 Watermelon ChargeTax 01 Yes ChargeTax 02 No ; run; DATA B; input (ID Crayon PayFine Fruit ChargeTax ) ( : $40.); cards; 12345 01 Yes 02 01 67891 03 No 02 02 00001 02 . 01 01 ; run; data want; if _n_ eq 1 then do; if 0 then set a; declare hash h(dataset:'a'); h.definekey('name','codes'); h.definedata('codevalues'); h.definedone(); end; set b; array x{*} $ Crayon--ChargeTax ; do i=1 to dim(x); name=vname(x{i});codes=x{i}; rc=h.find(); if rc=0 then x{i}=coalescec(codevalues,x{i}); end; drop NAME CODES CODEVALUES rc i; run; WORK.WANT ID Crayon PayFine Fruit ChargeTax 12345 Blue Yes Watermelon Yes 67891 Yellow No Watermelon No 00001 Red Apple Yes SAMPLE A: ID ID ID 12345 67891 00001 Crayon Crayon Crayon Blue Yellow Red PayFine PayFine PayFine Yes No Fruit Fruit Fruit Watermelon Watermelon Apple ChargeTax ChargeTax ChargeTax Yes No Yes

Dsrountree · ‎07-14-2014

Thanks Everyone - The problem is the input file that determines DATA A was provided to me by CMS (Government) so I can't change the input source. This file (DATA A) is currently being validated by an internal analyst looking to validate which variables to include in the report. I like both approaches, but it was easier to program the array since I have a list that will keep changing until she is done updating the template. Thanks for the input Tom, Jaap and Ksharp. Wait until I ask the next question - I'll post in a little while. "How to Flip/Transpose" my final data set ID Crayon PayFine Fruit ChargeTax 12345 Blue Yes Watermelon Yes 67891 Yellow No Watermelon No 00001 Red Apple Yes ID ID ID 12345 67891 00001 Crayon Crayon Crayon Blue Yellow Red PayFine PayFine PayFine Yes No Fruit Fruit Fruit Watermelon Watermelon Apple ChargeTax ChargeTax ChargeTax Yes No Yes

Dsrountree · ‎07-14-2014

The problem I'm having is DATA A set a is aleady defined and changes based on a selection made by another analyst who decides how many rows need to be included in the report. This is done through an Excel grid that is imported in and filtered on one field. Right now there's only 86 fields to include in the report via DATA A, but once she is done defining the fileds that may change to 1,000. Looking at the above examples means I would need to rebuild the program everytime she makes a change instead of referencing the data set created after the Excel file is imported. Keep in mind - I am new to SAS...

Dsrountree · ‎07-10-2014

Hello Tom, For HIPPA reasons I removed names, but you should understand the basics as to what i'm trying to accomplish. DATA B; In my situation was already created using a .SAS program supplied by vendor (60 seperate .SAS files that contain different column headers and data - below is one sample). How do I use the above code to reference NAME, CODES and CODEVALUES found in DATA A? The data sample above works great even though I had to change the code to look for CHAR instead of . when referencing the CODE field (field is CHAR/ has a mixture of numbers and text) Sample load from vendor: Look at PLANTYPE from both DATA A & DATA B and you'll see what my desired output is 48 MMP HMO 01 HMO That's just one field for all of the fields they supplied that need to be mapped back to DATA A. DATA B; length pbp_a_hnumber $5 pbp_a_plan_identifier $3 pbp_a_ben_cov $1 pbp_a_plan_type $2 orgtype $2 bid_id $13 pbp_b4a_maxenr_yn $1 pbp_b4a_maxenr_per $1 pbp_b4a_coins_yn $1 pbp_b4a_coins_wavdia_yn $1 pbp_b4a_coins_wavdia_dh $1 pbp_b4a_ded_yn $1 pbp_b4a_copay_yn $1 pbp_b4a_copay_wavdia_yn $1 pbp_b4a_copay_wavdia_mi_dh $1 pbp_b4a_ded_er_cstshrs_yn $1 pbp_b4a_ded_er_cstshrs $3 pbp_b4b_maxenr_yn $1 pbp_b4b_maxenr_type $1 pbp_b4b_maxenr_per $1 pbp_b4b_coins_yn $1 pbp_b4b_coins_wavdmc_yn $1 pbp_b4b_coins_wavd_mcdh $1 pbp_b4b_ded_yn $1 pbp_b4b_copay_yn $1 pbp_b4b_copay_wavdia_yn $1 pbp_b4b_copay_wavd_mcdh $1 pbp_b4c_bendesc_yn $1 pbp_b4c_bendesc_amo $1 pbp_b4c_wwc_maxplan_yn $1 pbp_b4c_wwc_maxplan_per $1 pbp_b4c_maxenr_yn $1 pbp_b4c_maxenr_per $1 pbp_b4c_coins_yn $1 pbp_b4c_coins_wavdww_yn $1 pbp_b4c_ded_yn $1 pbp_b4c_copay_yn $1 pbp_b4c_copay_wavdww_yn $1 ; infile '/windows/pbp_b4_emerg_urgent.txt' delimiter = '09'x dsd lrecl = 30000 firstobs=2 missover; input pbp_a_hnumber pbp_a_plan_identifier segment_id pbp_a_ben_cov pbp_a_plan_type orgtype bid_id version pbp_b4a_maxenr_yn pbp_b4a_maxenr_amt pbp_b4a_maxenr_per pbp_b4a_coins_yn pbp_b4a_coins_pct_mc_min pbp_b4a_coins_pct_mc_max pbp_b4a_coins_wavdia_yn pbp_b4a_coins_wavdia_dh pbp_b4a_coins_wavd_dh pbp_b4a_ded_yn pbp_b4a_ded_amt pbp_b4a_copay_yn pbp_b4a_copay_amt_mc_min pbp_b4a_copay_amt_mc_max pbp_b4a_copay_wavdia_yn pbp_b4a_copay_wavdia_mi_dh pbp_b4a_copay_wavd_dh pbp_b4a_ded_er_cstshrs_yn pbp_b4a_ded_er_cstshrs pbp_b4b_maxenr_yn pbp_b4b_maxenr_type pbp_b4b_maxenr_amt pbp_b4b_maxenr_per pbp_b4b_coins_yn pbp_b4b_coins_pct_mc_min pbp_b4b_coins_pct_mc_max pbp_b4b_coins_wavdmc_yn pbp_b4b_coins_wavd_mcdh pbp_b4b_coins_wavdmc_dh pbp_b4b_ded_yn pbp_b4b_ded_amt pbp_b4b_copay_yn pbp_b4b_copay_amt_mc_min pbp_b4b_copay_amt_mc_max pbp_b4b_copay_wavdia_yn pbp_b4b_copay_wavd_mcdh pbp_b4b_copay_wavdmc_dh pbp_b4c_bendesc_yn pbp_b4c_bendesc_amo pbp_b4c_wwc_maxplan_yn pbp_b4c_wwc_maxplan_amt pbp_b4c_wwc_maxplan_per pbp_b4c_maxenr_yn pbp_b4c_maxenr_amt pbp_b4c_maxenr_per pbp_b4c_coins_yn pbp_b4c_coins_pct_ww pbp_b4c_coins_wavdww_yn pbp_b4c_ded_yn pbp_b4c_ded_amt pbp_b4c_copay_yn pbp_b4c_copay_amt_ww pbp_b4c_copay_wavdww_yn ; label pbp_a_hnumber = 'H Number' pbp_a_plan_identifier = 'Plan ID' segment_id = 'Segment ID' pbp_a_ben_cov = 'Coverage Criteria' pbp_a_plan_type = 'Plan Type' orgtype = 'Organization Type' bid_id = 'Bid ID' version = 'Version Number' pbp_b4a_maxenr_yn = 'Emerg Care Max Enr YN' pbp_b4a_maxenr_amt = 'Emerg Care Max Enr Amt' pbp_b4a_maxenr_per = 'Emerg Care Max Enr Per' pbp_b4a_coins_yn = 'Emerg Care Coinsurance YN' pbp_b4a_coins_pct_mc_min = 'Emerg Care MC Min Coins Pct' pbp_b4a_coins_pct_mc_max = 'Emerg Care MC Max Coins Pct' pbp_b4a_coins_wavdia_yn = 'Emerg Care MC Coins Waived YN' pbp_b4a_coins_wavdia_dh = 'Emerg Care Coins Waived D/H' pbp_b4a_coins_wavd_dh = 'Emerg Care Coins Waived D/H' pbp_b4a_ded_yn = 'Emerg Care Deductible YN' pbp_b4a_ded_amt = 'Emerg Care Deductible Amt' pbp_b4a_copay_yn = 'Emerg Care Copayment YN' pbp_b4a_copay_amt_mc_min = 'Emerg Care MC Min Copay Amt' pbp_b4a_copay_amt_mc_max = 'Emerg Care MC Max Copay Amt' pbp_b4a_copay_wavdia_yn = 'Emerg Care MC Copay Waived YN' pbp_b4a_copay_wavdia_mi_dh = 'Emerg Care Copay Waived D/H' pbp_b4a_copay_wavd_dh = 'Emerg Care Copay Waived D/H' pbp_b4a_ded_er_cstshrs_yn = 'Emerg ER Cst Shr Deductible YN' pbp_b4a_ded_er_cstshrs = 'Emerg ER Cost Shr Deductibles' pbp_b4b_maxenr_yn = 'Urg Care Max Enr YN' pbp_b4b_maxenr_type = 'Urg Care Max Enr Type' pbp_b4b_maxenr_amt = 'Urg Care Max Enr Amt' pbp_b4b_maxenr_per = 'Urg Care Max Enr Per' pbp_b4b_coins_yn = 'Urg Care Coinsurance YN' pbp_b4b_coins_pct_mc_min = 'Urg Care MC Min Coins Pct' pbp_b4b_coins_pct_mc_max = 'Urg Care MC Max Coins Pct' pbp_b4b_coins_wavdmc_yn = 'Urg Care MC Coins Waived YN' pbp_b4b_coins_wavd_mcdh = 'Urg Care MC Coins Waived D/H' pbp_b4b_coins_wavdmc_dh = 'Urg Care MC Coins Waived D/H' pbp_b4b_ded_yn = 'Urg Care Deductible YN' pbp_b4b_ded_amt = 'Urg Care Deductible Amt' pbp_b4b_copay_yn = 'Urg Care Copayment YN' pbp_b4b_copay_amt_mc_min = 'Urg Care MC Min Copay Amt' pbp_b4b_copay_amt_mc_max = 'Urg Care MC Max Copay Amt' pbp_b4b_copay_wavdia_yn = 'Urg Care Copay Waived (IA) YN' pbp_b4b_copay_wavd_mcdh = 'Urg Care MC Copay Waived D/H' pbp_b4b_copay_wavdmc_dh = 'Urg Care MC Copay Waived D/H' pbp_b4c_bendesc_yn = 'Worldwide Cov Desc YN' pbp_b4c_bendesc_amo = 'Worldwide Cov Desc AMO' pbp_b4c_wwc_maxplan_yn = 'Worldwide Cov MaxPlan YN' pbp_b4c_wwc_maxplan_amt = 'Worldwide Cov MaxPlan Amt' pbp_b4c_wwc_maxplan_per = 'Worldwide Cov MaxPlan Per' pbp_b4c_maxenr_yn = 'Emerg Care Max Enr YN' pbp_b4c_maxenr_amt = 'Emerg Care Max Enr Amt' pbp_b4c_maxenr_per = 'Emerg Care Max Enr Per' pbp_b4c_coins_yn = 'Emerg Care Coinsurance YN' pbp_b4c_coins_pct_ww = 'Emerg Care WW Coins Pct' pbp_b4c_coins_wavdww_yn = 'WW Coins Waived YN' pbp_b4c_ded_yn = 'Emerg Care Deductible YN' pbp_b4c_ded_amt = 'Emerg Care Deductible Amt' pbp_b4c_copay_yn = 'Emerg Care Copayment YN' pbp_b4c_copay_amt_ww = 'Emerg Care WW Copay Amt' pbp_b4c_copay_wavdww_yn = 'WW Copay Waived YN' ; run; SAMPLE of A: NAME CODES CODEVALUES bid_id pbp_a_ben_cov 1 Part A and Part B pbp_a_ben_cov 2 Part B Only pbp_a_plan_type 1 HMO pbp_a_plan_type 18 1876 Cost pbp_a_plan_type 19 HCPP - 1833 Cost pbp_a_plan_type 2 HMOPOS pbp_a_plan_type 20 National Pace pbp_a_plan_type 29 Medicare Prescription Drug Plan pbp_a_plan_type 30 Employer/Union Only Direct Contract PDP pbp_a_plan_type 31 Regional PPO pbp_a_plan_type 32 Fallback pbp_a_plan_type 4 Local PPO pbp_a_plan_type 40 Employer/Union Only Direct Contract PFFS pbp_a_plan_type 42 RFB HMO pbp_a_plan_type 43 RFB HMOPOS pbp_a_plan_type 44 RFB Local PPO pbp_a_plan_type 45 RFB PSO (State License) pbp_a_plan_type 47 Employer Direct PPO pbp_a_plan_type 48 MMP HMO pbp_a_plan_type 49 MMP HMOPOS pbp_a_plan_type 5 PSO (State License) pbp_a_plan_type 7 MSA pbp_a_plan_type 8 RFB PFFS pbp_a_plan_type 9 PFFS pbp_b4a_coins_pct_mc_max pbp_b4a_coins_pct_mc_min pbp_b4a_coins_wavd_dh pbp_b4a_coins_wavdia_dh 1 Days pbp_b4a_coins_wavdia_dh 2 Hours pbp_b4a_coins_wavdia_yn 1 Yes pbp_b4a_coins_wavdia_yn 2 No pbp_b4a_coins_yn 1 Yes pbp_b4a_coins_yn 2 No SAMPLE of B: pbp_a_hnumber pbp_a_plan_identifier pbp_a_ben_cov pbp_a_plan_type orgtype bid_id pbp_b4a_maxenr_yn pbp_b4a_maxenr_per pbp_b4a_coins_yn pbp_b4a_coins_wavdia_yn pbp_b4a_coins_wavdia_dh pbp_b4a_ded_yn pbp_b4a_copay_yn pbp_b4a_copay_wavdia_yn pbp_b4a_copay_wavdia_mi_dh pbp_b4a_ded_er_cstshrs_yn pbp_b4a_ded_er_cstshrs pbp_b4b_maxenr_yn pbp_b4b_maxenr_type pbp_b4b_maxenr_per pbp_b4b_coins_yn pbp_b4b_coins_wavdmc_yn pbp_b4b_coins_wavd_mcdh pbp_b4b_ded_yn pbp_b4b_copay_yn pbp_b4b_copay_wavdia_yn pbp_b4b_copay_wavd_mcdh pbp_b4c_bendesc_yn pbp_b4c_bendesc_amo pbp_b4c_wwc_maxplan_yn pbp_b4c_wwc_maxplan_per pbp_b4c_maxenr_yn pbp_b4c_maxenr_per pbp_b4c_coins_yn pbp_b4c_coins_wavdww_yn pbp_b4c_ded_yn pbp_b4c_copay_yn pbp_b4c_copay_wavdww_yn segment_id version pbp_b4a_maxenr_amt pbp_b4a_coins_pct_mc_min pbp_b4a_coins_pct_mc_max pbp_b4a_coins_wavd_dh pbp_b4a_ded_amt pbp_b4a_copay_amt_mc_min pbp_b4a_copay_amt_mc_max pbp_b4a_copay_wavd_dh pbp_b4b_maxenr_amt pbp_b4b_coins_pct_mc_min pbp_b4b_coins_pct_mc_max pbp_b4b_coins_wavdmc_dh pbp_b4b_ded_amt pbp_b4b_copay_amt_mc_min pbp_b4b_copay_amt_mc_max pbp_b4b_copay_wavdmc_dh pbp_b4c_wwc_maxplan_amt pbp_b4c_maxenr_amt pbp_b4c_coins_pct_ww pbp_b4c_ded_amt pbp_b4c_copay_amt_ww 555 001 1 48 05 555 2 2 2 2 2 2 2 2 2 0 4 555 001 1 01 01 555 2 2 2 1 1 2 2 2 2 2 1 2 1 2 2 2 2 2 1 1 0 5 65 65 24 10 35 65 555 002 1 01 01 555 2 2 2 1 1 2 2 2 2 2 1 2 1 2 2 2 2 2 1 1 0 6 65 65 24 10 35 65 888 003 1 01 01 888 2 2 2 1 1 2 2 2 2 2 1 2 1 2 2 2 2 2 1 1 0 4 65 65 24 15 50 65 999 004 1 01 01 999 2 2 2 1 1 2 2 2 2 2 1 2 1 2 2 2 2 2 1 1 0 4 65 65 24 5 15 65 1010 005 1 01 01 1010 2 2 2 1 1 2 2 2 2 2 1 2 1 2 2 2 2 2 1 1 0 5 65 65 24 20 50 65 666 801 1 01 01 666 2 1 1 1 2 2 2 2 1 1 1 2 2 2 2 2 1 2 2 0 3 20 20 3 20 20 3 666 802 1 01 01 666 2 1 1 1 2 2 2 2 1 1 1 2 2 2 2 2 1 2 2 0 3 20 20 3 20 20 3 666 803 1 01 01 666 2 1 1 1 2 2 2 2 1 1 1 2 2 2 2 2 1 2 2 0 2 20 20 3 20 20 3

Dsrountree · ‎07-10-2014

Crayon PayFine Fruit ChargeTax These are only 4 out of the 65 variables that need to be validated against DATA A to pull the CODE and CODEVALUES. Once this logic is complete the same code is needed to run against another 40 tables with 65 to 80 variables. Keep in mind my last blurb - there are 65 variables that need to be checked. Keep in mind DATA A & DATA B are very large tables and DATA B has 65 column headers.

Dsrountree · ‎07-10-2014

Hello Everyone, I have a strange situation in SAS that I’ve never come across before. I have two data sets: · 1. Data Dictionary (DATA A) – which defines the numbers or codes reference in DATA B · 2. DATA B – the variable field(s) and the code(s) per each ID DATA A: NAME CODES CODEVALUES Crayon 01 Blue Crayon 02 Red Crayon 03 Yellow PayFine Fruit 01 Apple Fruit 02 Watermelon ChargeTax 01 Yes ChargeTax 02 No ……… DATA B: ID Crayon PayFine Fruit ChargeTax ……… 12345 01 Yes 02 01 67891 03 No 02 02 00001 02 01 01 Desired Results: 12345 67891 00001 Blue Yellow Red Yes No Watermelon Watermelon Apple Yes No Yes Things to notice: DATA B - PayFine has its own values that are not populated from DATA A. DATA A – null values may be present The Desired Results data have been Transposed. The part that is confusing to me is the how to link the content of DATA A to the Variable headers of DATA B. Notice how Crayon is defined in DATA A using NAME, CODES & CODEVALUES, then referenced as a column in DATA B calling on CODES to pull in the correct CODEVALUES. Keep in mind DATA A & DATA B are very large tables and DATA B has 65 column headers. Thanks for any assistance or guidance on this one. :smileyconfused:

Online Status	Offline
Date Last Visited	‎09-14-2020 12:04 AM

Re: FULLNAME to FIRST NAME; LAST NAME (via Contact Name or Business Co...

Re: FULLNAME to FIRST NAME; LAST NAME (via Contact Name or Business Co...

Re: FULLNAME to FIRST NAME; LAST NAME (via Contact Name or Business Co...

FULLNAME to FIRST NAME; LAST NAME (via Contact Name or Business Contac...

Re: Finding Like Values and Assigning them to a DATASET

Re: Finding Like Values and Assigning them to a DATASET

Finding Like Values and Assigning them to a DATASET

Proc Transpose - Removing dupes

Re: Macro naming via data set/report

Re: Macro naming via data set/report

Re: Finding Like Values and Assigning them to a DATASET

Re: Finding Like Values and Assigning them to a DATASET

Re: Finding Like Values and Assigning them to a DATASET

Re: MULTIPLE PROC REPORT OUTPUTS

Re: MULTIPLE PROC REPORT OUTPUTS

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel W...

Re: Compress or Strip

Re: MULTIPLE PROC REPORT OUTPUTS

MULTIPLE PROC REPORT OUTPUTS

Re: Data Set Manipulation

Re: Data Set Manipulation

Re: Data Set Manipulation

Re: Data Set Manipulation

Data Set Manipulation

Re: Transpose - Vertical Dataset

Re: Transpose - Vertical Dataset

Transpose - Vertical Dataset

Re: Data Variable Manipulation

Re: Data Variable Manipulation

Re: Data Variable Manipulation

Re: Data Variable Manipulation

Data Variable Manipulation