@ChrisNZ That's really helpful.  This is (probably) a stupid question, but how would I pull in fields from table B? ... View more

The underlying data didn't change.     And to clarify, the header of the column isn't the issue.   That still comes through.  The issue is the values in the column.  So instead of Pharmacy A we have PharmacyA, Pharmacy A to Z as PharmacyAtoZ instead.   I've tried increasing the length of the field so it shouldn't be that.  The SAS table is correct.  And the HTML version is correct.  I would agree it is excel, but other character fields aren't having the same issue.     ... View more

Here is the whole code.   I did try running the change to the label statement and it didn't change anything.     There are other long character values in this report with spaces that are writing to Excel correctly.   %let report_pgm =Rpt_Changes_Report; %let syscc =0; %let owner =; %let how_many =0; options orientation=landscape papersize =legal linesize=255; /* 03-20-2017 */ %include "&main_dir/includes/Ref_&report_pgm..sas" / source2; proc format; picture pctpic low -< 0 ='009.99%' (prefix='-' mult=10000) 0 - high ='099.99%' (mult=10000) ; run; %macro loopthru (owner=, prefix=, dsname=); proc sql; create table Changes_Report_&owner (drop =changesx) as select distinct DrugInfo.Core_Category, DrugInfo.GPI AS GPI_Number, DrugInfo.Generic_Name, DrugInfo.NDC, DrugInfo.Brand_Name, DrugInfo.Brand_Generic, DrugInfo.ROA, PharRate.Rate_1 AS Exclusive_Rate, PharRate.Price_Schedule_1 AS Price_Schedule_1, PharRate.Rate_2 AS Non_Exclusive_Rate, PharRate.Price_Schedule_2 AS Price_Schedule_2, PharRate.Rate_3 AS Non_ASO_Rate, /* new */ PharRate.Price_Schedule_3 AS Price_Schedule_3, /* new */ PharRate.PDL_Rate, PharRate.PDL_PS, PharRate.Retail_PDL_Rate, PharRate.Retail_PDL_PS, PharRate.PDL_IND, PharRate.Retail_PDL_IND, DrugInfo.Jcode, PharRate.Price_Level, FeeInfo.DML, FeeInfo.Specialty_Pharm_Med_Both AS Pharm_Med_Both, FeeInfo.Oral_Onc_Zero_Copay AS Oral_Onc_Zero_Copay, PharRate.Pharmacy_Name, DrugInfo.Specialty_Drug_Status, PharRate.Specialty_Rate_1_Type, PharRate.Specialty_Rate_2_Type, PharRate.Specialty_Rate_3_Type, /* new */ FeeInfo.Specialty_Limited_Distribution, DrugInfo.Change_Text AS drug_change_text, feeInfo.Change_Text AS fee_change_text, PharRate.Change_Text AS rate_change_text, DrugInfo.Change_Text || ", " || FeeInfo.Change_Text || ", " || PharRate.Change_Text AS changesX format =$300., case compbl(calculated changesX) when ",, , ,, , ,," then ", ," else calculated changesX end AS changes format =$300., put(datepart(druginfo.lastupdate_date),yymmd7.) AS drug_date, put(datepart(feeinfo.lastupdate_date), yymmd7.) AS fee_date, put(datepart(pharrate.lastupdate_date),yymmd7.) AS rate_date FROM sas_data.Client_Information ClntInfo INNER JOIN ( ( sas_data.Drug_Information_upd DrugInfo INNER JOIN sas_data.Pharmacy_Rate_upd PharRate ON DrugInfo.NDC =PharRate.NDC ) INNER JOIN sas_data.Fee_Information_upd FeeInfo ON DrugInfo.NDC =FeeInfo.NDC ) ON (ClntInfo.List_Owner_Key =FeeInfo.List_Owner_Key) AND (ClntInfo.List_Owner_Key =PharRate.List_Owner_Key) WHERE FeeInfo.List_Owner_Key =&owner AND PharRate.List_Owner_Key =&owner AND ("&Process_Mth" =calculated drug_date or "&Process_Mth" =calculated fee_date or "&Process_Mth" =calculated rate_date ) GROUP BY DrugInfo.Core_Category, DrugInfo.GPI, DrugInfo.Generic_Name, DrugInfo.NDC, DrugInfo.Brand_Name, DrugInfo.Brand_Generic, DrugInfo.ROA, PharRate.Rate_1, PharRate.Price_Schedule_1, PharRate.Rate_2, PharRate.Price_Schedule_2, PharRate.Rate_3, /* new */ PharRate.Price_Schedule_3, /* new */ PharRate.PDL_Rate, PharRate.PDL_PS, PharRate.Retail_PDL_Rate, PharRate.Retail_PDL_PS, PharRate.PDL_IND, PharRate.Retail_PDL_IND, DrugInfo.Jcode, PharRate.Price_Level, FeeInfo.DML, FeeInfo.Specialty_Pharm_Med_Both, FeeInfo.Oral_Onc_Zero_Copay, PharRate.Pharmacy_Name, DrugInfo.Specialty_Drug_Status, PharRate.Specialty_Rate_1_Type, PharRate.Specialty_Rate_2_Type, PharRate.Specialty_Rate_3_Type, /* new */ FeeInfo.Specialty_Limited_Distribution, DrugInfo.Change_Text || ", " || FeeInfo.Change_Text || ", " || PharRate.Change_Text, FeeInfo.Change_Text, DrugInfo.Change_Text, PharRate.Change_Text, ClntInfo.List_Owner_Key, FeeInfo.List_Owner_Key, PharRate.List_Owner_Key, FeeInfo.LastUpdate_Date, DrugInfo.LastUpdate_Date, PharRate.LastUpdate_Date Order By ( (( FeeInfo.Change_Text) <> "") AND (( ClntInfo.List_Owner_Key) =&owner) AND (( FeeInfo.List_Owner_Key) =&owner) AND (( PharRate.List_Owner_Key) =&owner) AND (( calculated fee_date ="&Process_Mth"))) OR ( (( DrugInfo.Change_Text) <> "") AND (( ClntInfo.List_Owner_Key) =&owner) AND (( FeeInfo.List_Owner_Key) =&owner) AND (( PharRate.List_Owner_Key) =&owner) AND (( calculated drug_date ="&Process_Mth"))) OR ( (( PharRate.Change_Text) <> "") AND (( ClntInfo.List_Owner_Key) =&owner) AND (( FeeInfo.List_Owner_Key) =&owner) AND (( PharRate.List_Owner_Key) =&owner) AND (( calculated rate_date ="&Process_Mth"))) OR ( (( ClntInfo.List_Owner_Key) =&owner) AND (( FeeInfo.List_Owner_Key) =&owner) AND (( PharRate.List_Owner_Key) =&owner) AND (( calculated fee_date ="&Process_Mth"))) OR ( (( ClntInfo.List_Owner_Key) =&owner) AND (( FeeInfo.List_Owner_Key) =&owner) AND (( PharRate.List_Owner_Key) =&owner) AND (( calculated drug_date ="&Process_Mth"))) OR ( (( ClntInfo.List_Owner_Key) =&owner) AND (( FeeInfo.List_Owner_Key) =&owner) AND (( PharRate.List_Owner_Key) =&owner) AND (( calculated rate_date ="&Process_Mth"))) ; quit; /**************************************************/ /*** recalc change fields, case issues in sql. ***/ /*** because of data step, quotes must be added ***/ /**************************************************/ data Changes_Report_&owner; set Changes_Report_&owner; /* 11-21-2016 comment out changes here */ length /*changes */ changes_old $300 drugfnd feefnd ratefnd $1 mix $3; changes_old =changes; retain prfx_drug "'"; retain prfx_fee ", '"; retain prfx_rate ", , '"; retain blk_quote "~'"; retain sufx_drug "', ,"; retain sufx_fee "',"; retain sufx_rate "',"; changes =' '; drugfnd ='-'; feefnd ='-'; ratefnd ='-'; mix ='---'; if "&process_mth" =drug_date then do; if length(drug_change_text) >10 then drugfnd ='D'; end; if "&process_mth" =fee_date then do; if length(fee_change_text) >10 then feefnd ='F'; end; if "&process_mth" =rate_date then do; if length(rate_change_text) >10 then ratefnd ='R'; end; mix =drugfnd !! feefnd !! ratefnd; if mix ='D--' then do; changes =cats(prfx_drug,drug_change_text); lgth =length(changes); if substr(changes,lgth,1)=',' then changes =substr(changes,1,lgth-1); /*** drop comma ***/ changes =cats(changes,sufx_drug); /*** cat quote,comma ***/ end; else if mix ='DF-' then do; lgth_d =length(drug_change_text); lgth_f =length(fee_change_text); if substr(drug_change_text,lgth_d,1)=',' then drug_change_text =substr(drug_change_text,1,lgth_d-1); /** drop comma **/ drug_change_text =cats(prfx_drug,drug_change_text,sufx_fee); if substr(fee_change_text,lgth_d,1)=',' then fee_change_text =substr(fee_change_text,1,lgth_f-1); /* drop comma */ fee_change_text =cats(fee_change_text,sufx_fee); /* quote,comma */ changes =cats(drug_change_text, blk_quote, fee_change_text); end; else if mix ='DFR' then do; lgth_d =length(drug_change_text); lgth_f =length(fee_change_text); lgth_r =length(rate_change_text); if substr(drug_change_text,lgth_d,1)=',' then drug_change_text =substr(drug_change_text,1,lgth_d-1); /** drop comma **/ drug_change_text =cats(prfx_drug,drug_change_text,sufx_fee); if substr(fee_change_text,lgth_f,1) =',' then fee_change_text =substr(fee_change_text,1,lgth_f-1); /* drop comma */ fee_change_text =cats(fee_change_text,sufx_fee); /* quote,comma */ if substr(rate_change_text,lgth_r,1)=',' then rate_change_text =substr(rate_change_text,1,lgth_r-1); /* drop comma */ rate_change_text =cats(fee_change_text,sufx_fee); /* quote,comma*/ changes =cats(drug_change_text, blk_quote, fee_change_text, blk_quote, rate_change_text ); end; else if mix ='-FR' then do; lgth_f =length(fee_change_text); lgth_r =length(rate_change_text); if substr(fee_change_text,lgth_f,1) =',' then fee_change_text =substr(fee_change_text,1,lgth_f-1); /* drop comma */ fee_change_text =cats(prfx_fee,fee_change_text,sufx_fee); /* quote,comma*/ if substr(rate_change_text,lgth_r,1)=',' then rate_change_text =substr(rate_change_text,1,lgth_r-1); /* drop comma */ rate_change_text =cats(fee_change_text,sufx_fee); /* quote,comma */ changes =cats(fee_change_text, blk_quote, rate_change_text ); end; else if mix ='D-R' then do; lgth_d =length(drug_change_text); lgth_r =length(rate_change_text); if substr(drug_change_text,lgth_d,1)=',' then drug_change_text =substr(drug_change_text,1,lgth_d-1); /** drop comma **/ drug_change_text =cats(prfx_drug,drug_change_text,sufx_fee); if substr(rate_change_text,lgth_r,1)=',' then rate_change_text =substr(rate_change_text,1,lgth_r-1); /* drop comma */ rate_change_text =cats(fee_change_text,sufx_fee); /* quote,comma */ changes =cats(drug_change_text, blk_quote, rate_change_text ); end; else if mix ='-F-' then do; changes =cats(prfx_fee,fee_change_text); lgth =length(changes); if substr(changes,lgth,1)=',' then changes =substr(changes,1,lgth-1); /*** drop comma ***/ changes =cats(changes,sufx_fee); /*** cat quote,comma ***/ end; else if mix ='--R' then do; changes =cats(prfx_rate,rate_change_text); lgth =length(changes); if substr(changes,lgth,1)=',' then changes =substr(changes,1,lgth-1); /*** drop comma ***/ changes =cats(changes,sufx_rate); /*** cat quote,comma ***/ end; else if mix ='---' then changes =", ,"; changes =translate(changes," ","~"); drop changes_old prfx_drug prfx_fee prfx_rate blk_quote sufx_drug sufx_fee sufx_rate drugfnd feefnd ratefnd mix; output; run; proc sql noprint; select count(*) into :how_many from Changes_Report_&owner; quit; %if &how_many =0 %then %do; data Changes_Report_Empty; core_category ='0 rows found'; output; stop; run; data Changes_Report_&owner; set Changes_Report_&owner Changes_Report_Empty; output; stop; run; %end; filename ref "&main_pdf/&dsname.__&process_mth2..xlsx"; ods listing close; ods html close; ods Excel file =ref style =seaside options( center_horizontal ="yes" embedded_footnotes ="yes" embedded_titles ="yes" frozen_headers ="5" orientation ='landscape' sheet_name ="Change Report" ); data _null_; length Changes $20; Changes ='Changes'; nulls ='0A0A0A0A0A0A0A0A0A0A'x; do ii =1 to 1; ***30; Changes =trim(left(Changes)) || nulls; end; call symput ('changes',Changes); run; %let prefixx =%sysfunc(translate(&prefix,_,-)); %let dsnamex =%sysfunc(translate(&dsname,_,-)); data reptdata.&dsnamex; set Changes_Report_&owner; changes =compbl(changes); run; data Changes_Report_&owner; length exclusive_ratex non_exclusive_ratex non_aso_ratex /* new */ $8; set Changes_Report_&owner end =eof; Changes =compbl(changes); retain max_pn 0 max_cc 0 max_gn 0 max_bn 0 max_jc 0 max_ch 0 ; drop max_pn max_cc max_gn max_bn max_jc max_ch; max_pn =max(max_pn,length(trim(left(pharmacy_name)))); max_cc =max(max_cc,length(trim(left(core_category)))); max_gn =max(max_gn,length(trim(left(generic_name)))); max_bn =max(max_bn,length(trim(left(brand_name)))); max_ch =max(max_ch,length(trim(left(changes)))); max_jc =max(max_jc,length(trim(left(jcode)))); if eof then do; put max_pn= max_cc= max_gn= max_bn= max_jc= max_ch=; call symput('max_pn', compress(put(max_pn+2,3.))); call symput('max_cc', compress(put(max_cc+2,3.))); call symput('max_gn', compress(put(max_gn+2,3.))); call symput('max_bn', compress(put(max_bn+2,3.))); call symput('max_ch', compress(put(max_ch+2,3.))); call symput('max_jc', compress(put(max_jc+2,3.))); end; if changes =' ' then delete; /*** both new. 2015_06-17 ***/ if index(changes,'ADDED')> 0 and index(changes,'ADDED')< 11 then /*** ADDED starts betw 1 and 10 **/ changes =",,"; /* 11-04-2016 Add NON ASO vars. */ /* 08-02-2016 rate variable fixup */ /* convert num-to-char */ exclusive_ratex = put(exclusive_rate, pctpic.); non_exclusive_ratex = put(non_exclusive_rate, pctpic.); non_aso_ratex = put(non_aso_rate, pctpic.); /* new ! */ pdl_ratex =put(pdl_rate, pctpic.); retail_pdl_ratex =put(retail_pdl_rate, pctpic.); run; title1 j=left font=Arial height=14pt bold "&prefix."; title2 j=left font=Arial height=10pt color=white bcolor=black "&rundte"; title3 " "; /* 11-04-2016 add NON ASO vars. */ proc print data =Changes_Report_&owner split='*' noobs; /* 05-10-2017 */ where ndc not in (&ndc_exclude_list); var /* 01 */ core_category / style(data)={tagattr='wrap:no'} ; var /* 02 */ gpi_number / style(data)={tagattr="format:@"} ; var /* 03 */ generic_name / style(data)={tagattr='wrap:no'} ; ; var /* 04 */ ndc / style(data)={tagattr="format:@"} ; var /* 05 */ brand_name / style(data)={tagattr='wrap:no'} ; var /* 06 */ brand_generic / style={just=c} ; var /* 07 */ roa / style={just=c} ; var /* 08 */ exclusive_ratex / style={just=c} ; var /* 09 */ price_schedule_1 ; var /* 10 */ non_exclusive_ratex / style={just=c} ; var /* 11 */ price_schedule_2 ; var /* 12 */ non_aso_ratex / style={just=c} ; /* new ! */ var /* 13 */ price_schedule_3 ; /* new ! */ var PDL_IND ; var /* 12 */ PDL_Ratex / style={just=c} ; var /* 12 */ PDL_PS ; var Retail_PDL_IND ; var /* 12 */ Retail_PDL_Ratex / style={just=c} ; var /* 12 */ Retail_PDL_PS ; var /* 14 */ jcode / style={just=c} ; var /* 15 */ price_level / style={just=c} ; var /* 16 */ specialty_limited_distribution / style={just=c} ; var /* 17 */ dml / style={just=c} ; var /* 18 */ pharm_med_both / style={just=c} ; var /* 21 */ oral_onc_zero_copay / style={just=c} ; var /* 23 */ pharmacy_name / style(data)={tagattr='wrap:no'} ; var /* 23 */ specialty_drug_status ; var /* 26 */ changes / style(data)={tagattr='wrap:yes'} ; /* 03-03-2017 */ format /* 01 */ core_category $&max_cc.. ; format /* 02 */ gpi_number $14. ; format /* 03 */ generic_name $&max_gn.. ; format /* 04 */ ndc $11. ; format /* 05 */ brand_name $&max_bn.. ; format /* 06 */ brand_generic $3. ; format /* 07 */ roa $5. ; format /* 09 */ price_schedule_1 $9. ; format /* 11 */ price_schedule_2 $9. ; format /* 13 */ price_schedule_3 $9. ; /* new ! */ format /* 13 */ PDL_PS $10. ; format /* 13 */ Retail_PDL_PS $10. ; format PDL_IND $3.; format Retail_PDL_IND $3.; format /* 14 */ jcode $&max_jc.. ; format /* 23 */ pharmacy_name $&max_pn.. ; format /* 23 */ specialty_drug_status $16. ; format /* 24 */ changes $&max_ch.. ; label /* 01 */ core_category ="Core*Category" ; label /* 02 */ gpi_number ="GPI*Number" ; label /* 03 */ generic_name ="Generic*Name" ; label /* 05 */ brand_name ="Brand*Name" ; label /* 06 */ brand_generic ="Brand*Generic" ; label /* 07 */ roa ="ROA" ; label /* 08 */ exclusive_ratex ="Exclusive*Rate" ; label /* 09 */ price_schedule_1 ="Price*Schedule 1" ; label /* 10 */ non_exclusive_ratex ="Non-*Exclusive*Rate" ; label /* 11 */ price_schedule_2 ="Price*Schedule 2" ; label /* 12 */ non_aso_ratex ="Non ASO*and*Med*Billing*Rate" ; /* new ! */ label /* 13 */ price_schedule_3 ="Non ASO*and*Med*Billing*Price*Schedule" ; /* new ! */ label /* 12 */ PDL_Ratex ="PDL*Rate" ; label /* 12 */ PDL_PS ="PDL*Price*Schedule" ; label /* 12 */ Retail_PDL_Ratex ="Retail*PDL*Rate" ; label /* 12 */ Retail_PDL_PS ="Retail*PDL*Price*Schedule" ; label PDL_IND="PDL Indicator"; label Retail_PDL_IND="Retail PDL Indicator"; label /* 15 */ price_level ="Price*Level" ; label /* 16 */ specialty_limited_distribution ="Specialty*Limited*Distribution" ; label /* 19 */ pharm_med_both ="Pharm/Med/*Both" ; label /* 21 */ oral_onc_zero_copay ="Oral Onc*Zero Copay" ; label /* 23 */ pharmacy_name ='Pharmacy Name' ; label /* 23 */ specialty_drug_status ="Drug*Status" ; label /* 26 */ changes ="&Changes" ; run; title; footnote; ods excel close; /**/ proc delete data=Changes_Report_&owner ; run; %mend loopthru; %macro control; %do ii =1 %to &owner_max; %loopthru ( owner =&&owner&ii, prefix =&&file_prefix&ii, dsname =&&dsn&ii ); %end; %mend control; %control   ... View more