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;
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.
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;
I replaced the _numeric_ to _character_ in the loop. It is now working. Thank you and have a good weekend
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.