topic Re: ODS EXCEL SAS EG 8.2 how to remove space in the output when character variable is blank? in ODS and Base Reporting

ODS EXCEL SAS EG 8.2 how to remove space in the output when character variable is blank?

1239 — Wed, 11 Nov 2020 14:18:42 GMT

how to remove space in the output when character variable is blank using ODS EXCEL? I have provided the below sample code and attached excel output file for your quick reference. Kindly note that formula used in "Column C" from test1.xslx file shows that "siteid" has single space inserted but I want to remove this. I am looking for a solution using ODS EXCEL in SAS EG 8.2.

data have ;
  length SITEID $5 SUBJID $20 ;
  infile cards dsd truncover ;
  input siteid subjid ;

cards;
,102-002-001
,102-008-002
,102-008-008
,102-02-002
;
run;

ods excel file="c:\temp\test1.xlsx" ;
proc print data=have noobs label;
run;
ods excel close ;

Re: ODS EXCEL SAS EG 8.2 how to remove space in the output when character variable is blank?

Cynthia_sas — Thu, 12 Nov 2020 00:11:04 GMT

Hi:
Enterprise Guide is not impacting your results one way or the other. You have defined SITEID as character with a length of $5. That means when SITEID is missing (as it is on all 5 rows), SAS represents a missing character variable value with 1 space. SAS represents a missing numeric variable with 1 period or .

Your SITEID is missing on all 5 rows. SAS represents that with 1 blank. That is the definition of a missing character variable.
Cynthia

Re: ODS EXCEL SAS EG 8.2 how to remove space in the output when character variable is blank?

1239 — Thu, 12 Nov 2020 04:31:51 GMT

Hi, Thanks for your reply!

In my organization, SAS EG 8.2 is installed and SAS EG 7.1 is going to retired shortly and I need to compare the previous output generated in SAS EG 7.1 and current output generated in SAS EG 8.2 for reconciliation report and this extra blank in SAS EG 8.2 is troubling to reconcile the output as it won't match.

Here is the below snapshot of length of "siteid" from both SAS EG 8.2 and SAS EG 7.1 which shows that SAS EG 8.2 is inserting blank value for character variable however it's not in SAS EG 7.1. Please help.

SAS EG 8.2 output length:

SAS EG 7.1 output length:

Re: ODS EXCEL SAS EG 8.2 how to remove space in the output when character variable is blank?

Cynthia_sas — Thu, 12 Nov 2020 20:31:19 GMT

Hi:
If you are finding differences in output when you quality check the SAME code with the SAME data running with EG 7.1 versus EG 8.2, you'll need to work with Tech Support. They will need to verify what version of SAS is running behind the scenes on the server that EG is submitting code to. If this is a defect or something for which there is a workaround, they will be able to find out from the developers.
Cynthia