Writing to Excel Removes Spaces from Values

[Albr0166]

Have a project that I inherited from someone else that involves updating various SAS data sets and creating reports in Excel.  This month when I ran it, despite no changes there are spaces that are removed from the values in one column.  Instead of being "Pharmacy Name" it is "PharmacyName".   The underlying SAS dataset shows the spaces.  The Excel does not.

I'm running this in code on SAS EG 7.15 HF8 (7.100.5.6214)

This is the for the ODS.

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

Here is the relevant part from the output

 var    /* 22 */ pharmacy_name                   / style(data)={tagattr='wrap:no'}   ;   
  format /* 22 */ pharmacy_name                   $30.                                ;   
     label  /* 22 */ pharmacy_name                   ="Pharmacy*Name  "                  ; 

Any thoughts on what the issue is?  As I said, I ran this a month ago with no issues and didn't change the code.

[Reeza]

There should be a line about splitchar somewhere. Did you forget to run that?

Also if you have Office 365 it is updated continuously so something could have changed in Excel not necessarily SAS.

[ballardw]

You should show the code for an entire Procedure call. Often other options in a procedure will affect any specific variable.

I have to assume that you are attempting to use Splitchar in some form in the procedure. That would typically result in the label appearing on two lines in a report column heading, ie

Pharmacy

Name

however you have a tagattr:'wrap:no' which removes the ability to appear as stacked text. The actual split character space does not appear in the label so the tagattr setting is removing the vertical split leaving no space between words.

So try replacing the * in the label with a simple space and see if you get the desired result.

[Albr0166]

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 

[Reeza]

I'm really inclined to think this is an excel change. But...is the data changing? Is it possible the width of the column is now to short because the data is not wide enough to wrap the name. SInce it's a business name I find that unlikely but it's a possibility. Try removing the * and see what happens. But if the code didn't change I'm very much leaning towards an Excel change.

If you can try running it with previous months data to see if it still causes the same issue.

[Albr0166]

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.

[Reeza]

Ok, that's different. Your code doesn't show any lengths at all, where are you setting the widths? Search your code for a COMPRESS or COMPBL function - those remove spaces. I'd be very interested in seeing a screenshot of the various output. I wonder if the spaces are not spaces but a different character, try displaying them with a hex code and see what it shows. A space is 20, 0A is a carriage return.

[TomKari]

Just for fun, I tried to hack your code down to the minimum that might demonstrate the problem. I ran the following, and it worked fine...all three pharmacy names have spaces in them in the spreadsheet. Could I ask you to run it, and let us know how the variables look in your spreadsheet.

Tom

options orientation=landscape  papersize  =legal linesize=255;  /*  03-20-2017  */

data Inter01;
length Pharmacy_Name $100;
input;
Pharmacy_Name = _infile_;
cards;
Pharmacy Name 1
Pharmacy Name*2
Pharmacy Name 3
run;

filename ref "Inter02.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 Inter03;
        set Inter01 end =eof;  

        retain max_pn 0;   
        drop   max_pn;

		max_pn =max(max_pn,length(trim(left(pharmacy_name))));

        if eof then do; 
             put  max_pn=;
			 call symput('max_pn', compress(put(max_pn+2,3.)));
			 end;
    run;

     title1 j=left font=Arial  height=14pt  bold  "Title 1";
     title2 j=left font=Arial  height=10pt  color=white   bcolor=black   "Title 2";
     title3 " ";

    /* 11-04-2016  add NON ASO vars. */
    proc print data =Inter03  split='*' noobs;

        var    /* 23 */ pharmacy_name                       / style(data)={tagattr='wrap:no'}  ;                 
            format /* 23 */ pharmacy_name                       $&max_pn..                          ;                                   
        label /* 23 */ pharmacy_name                        ='Pharmacy Name'                    ;         
    run;

    title;
    footnote;

    ods excel close;

[ballardw]

---

@Albr0166 wrote:

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.

---

Data.

Actual data that shows that behavior.

As in the data from the Proc Print step.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.