topic Too many dots in the diff line for just two fields in SAS Programming

Too many dots in the diff line for just two fields

hannahl37 — Wed, 01 Jun 2016 17:16:25 GMT

Morning everyone,

I have a simple compare program in EG that is suppose to output the compared results to Excel. Two of those fields are marked with lines of way to many dots, way to many ! Please see below. Should be just couple of dots likes the other columns to mark the two compared the fields are having the same values. Because of the dots, my output Excel file become huge in size. Any clues as what maybe happening? I am attaching the screens shots of the code and output.

Let me know when you have a chance. Thanks for your time guiding me with SAS.

proc compare base=&temp..&tbl_name._stg&src_stg.
compare=&temp..&tbl_name._trg&src_trg.
out=outdiffpm outnoequal outbase outcomp outdif noprint;
/* out=outdiffpm outnoequal outbase outcomp noprint;*/
TITLE1 "&src_sys. - &tbl_name. Base (Staging Not in Target) - Compare (Target Not in Staging) - &sysdate9.";
TITLE2 color=red justify=left "src_sys_key_1_tx = xxrgn_id (16=CO,28=GA,12=HI,10=NW,14=OH)";
TITLE3 color=red justify=left "src_sys_key_2_tx = MSMELG0T.INDV_HRN";
TITLE4 color=red justify=left "src_sys_key_3_tx = MSMELG0T.GRP_ID";
TITLE5 color=red justify=left "src_sys_key_4_tx = MSMELG0T.SGRP_ID";
TITLE6 color=red justify=left "src_sys_key_5_tx = MSMELG0T.faml_subscr_id";
TITLE7 color=red justify=left "src_sys_key_6_tx = MSMELG0T.mbr_eff_dt";
TITLE8 color=red justify=left "src_sys_key_7_tx = MSMELG0T.mbr_can_dt";

/*ID rgn_cd
src_sys_key_1_tx
src_sys_key_2_tx
src_sys_key_3_tx;
VAR rundate;*/
run;

ods html FILE="&path.CO_&tbl_name._pcomp_outdiffpm_&sysdate9..xls"
headtext="<style> td {mso-number-format:\@}</style>";

proc print data=outdiffpm;
run;

ods html close;

Re: Too many dots in the diff line for just two fields

ballardw — Wed, 01 Jun 2016 17:27:28 GMT

Without any input data sets it's a bit hard to point to specifics.

First guess: those values that look like dates are character and actually longer than they appear. Since those variables end in DT one suspects they were originally DATETIME variables with around 20 characters. The ... match the length of the variable.

You could take the outdiffpm dataset through a datastep and change the value from ............... to .... as desired.

Re: Too many dots in the diff line for just two fields

hannahl37 — Fri, 03 Jun 2016 20:48:41 GMT

Hi,

I have added some code in the orignal attempting replacing the 'too many dots' to just 4 dots. The code is below, it somehow does not do the tricks. Can you let me know if there is anything worng ? I can provide more details if you need me send logs or etc. Just not sure if you need to diagonose this.

thanks

proc compare base=&temp..&tbl_name._stg&src_stg.
compare=&temp..&tbl_name._trg&src_trg.
out=outdiffpm outnoequal outbase outcomp outdif noprint;
TITLE1 "Compare.";

run;

data outdiffpm;
set outdiffpm;
array Var _numeric_;
do over Var;
if Var=: '.' then Var='....';
end;
run ;

ods html FILE="&path.CO_&tbl_name._pcomp_outdiffpm_&sysdate9..xls"
headtext="<style> td {mso-number-format:\@}</style>";
proc print data=outdiffpm;
run;

ods html close;

Re: Too many dots in the diff line for just two fields

hannahl37 — Fri, 03 Jun 2016 20:53:58 GMT

Here is the log

1 The SAS System 13:31 Friday, June 3, 2016

1 %_eg_hidenotesandsource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
5 %_eg_hidenotesandsource;
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "C:\Users\F245210\AppData\Local\Temp\SEG8480\SAS Temporary
Files\_TD9048_CNPTCAM3492313_\Prc2/"
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "C:\Users\F245210\AppData\Local\Temp\SEG8480\SAS Temporary
Files\_TD9048_CNPTCAM3492313_\Prc2/"
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
38
39 proc compare base=&temp..&tbl_name._stg&src_stg.
SYMBOLGEN: Macro variable TEMP resolves to kaki
SYMBOLGEN: Macro variable TBL_NAME resolves to mbrshp
SYMBOLGEN: Macro variable SRC_STG resolves to O
40 compare=&temp..&tbl_name._trg&src_trg.
SYMBOLGEN: Macro variable TEMP resolves to kaki
SYMBOLGEN: Macro variable TBL_NAME resolves to mbrshp
SYMBOLGEN: Macro variable SRC_TRG resolves to O
41 out=outdiffpm outnoequal outbase outcomp outdif noprint;
SYMBOLGEN: Macro variable SRC_SYS resolves to CM
SYMBOLGEN: Macro variable TBL_NAME resolves to mbrshp
SYMBOLGEN: Macro variable SYSDATE9 resolves to 03JUN2016
42 TITLE1 "&src_sys. - &tbl_name. Base (Staging Not in Target) - Compare (Target Not in Staging) - &sysdate9.";
43 TITLE2 color=red justify=left "src_sys_key_1_tx = xxrgn_id (16=CO,28=GA,12=HI,10=NW,14=OH)";
44 TITLE3 color=red justify=left "src_sys_key_2_tx = MSMELG0T.INDV_HRN";
45 TITLE4 color=red justify=left "src_sys_key_3_tx = MSMELG0T.GRP_ID";
46 TITLE5 color=red justify=left "src_sys_key_4_tx = MSMELG0T.SGRP_ID";
47 TITLE6 color=red justify=left "src_sys_key_5_tx = MSMELG0T.faml_subscr_id";
48 TITLE7 color=red justify=left "src_sys_key_6_tx = MSMELG0T.mbr_eff_dt";
49 TITLE8 color=red justify=left "src_sys_key_7_tx = MSMELG0T.mbr_can_dt";
50
51 run;

NOTE: There were 4 observations read from the data set KAKI.MBRSHP_STGO.
NOTE: There were 4 observations read from the data set KAKI.MBRSHP_TRGO.
NOTE: The data set WORK.OUTDIFFPM has 12 observations and 42 variables.
NOTE: Compressing data set WORK.OUTDIFFPM decreased size by 0.00 percent.
Compressed is 1 pages; un-compressed would require 1 pages.
NOTE: PROCEDURE COMPARE used (Total process time):
real time 0.12 seconds
cpu time 0.03 seconds

52 data outdiffpm;
53 set outdiffpm;
54 array Var _numeric_;
55 do over Var;
56 if Var=: '.' then Var='....';
57 end;
58 run ;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
56:22 56:31
NOTE: There were 12 observations read from the data set WORK.OUTDIFFPM.
NOTE: The data set WORK.OUTDIFFPM has 12 observations and 42 variables.
NOTE: Compressing data set WORK.OUTDIFFPM decreased size by 0.00 percent.
Compressed is 1 pages; un-compressed would require 1 pages.
2 The SAS System 13:31 Friday, June 3, 2016

NOTE: DATA statement used (Total process time):
real time 0.13 seconds
cpu time 0.03 seconds

59
60
61 ods html FILE="&path.CO_&tbl_name._pcomp_outdiffpm_&sysdate9..xls"
SYMBOLGEN: Macro variable PATH resolves to C:\SAS\OOB\CM\
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable TBL_NAME resolves to mbrshp
SYMBOLGEN: Macro variable SYSDATE9 resolves to 03JUN2016
62 headtext="<style> td {mso-number-format:\@}</style>";
NOTE: Writing HTML Body file: C:\SAS\OOB\CM\CO_mbrshp_pcomp_outdiffpm_03JUN2016.xls
63 /*proc export */
64 /* data=outdiffpm (obs=100)*/
65 /* dbms=xlsx */
66 /* outfile="&path.CO_&tbl_name._pcomp_outdiffpm_&sysdate9..xls"*/
67 /* if string=: '.' then string=substr(string,1,3); */
68 /* replace;*/
69 /*run;*/
70
71 proc print data=outdiffpm;
72 run;

NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT.
NOTE: There were 12 observations read from the data set WORK.OUTDIFFPM.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.40 seconds
cpu time 0.09 seconds

73
74 ods html close;
75
76
77
78 %_eg_hidenotesandsource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
90
91
92 %_eg_hidenotesandsource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
95

Re: Too many dots in the diff line for just two fields

hannahl37 — Fri, 03 Jun 2016 23:24:26 GMT

I replaced the _numeric_ to _character_ in the loop. It is now working. Thank you and have a good weekend