I will keep it in mind, thanks ... View more

hi,    My entire program involved 3 data source. one (1) is import from text file and two (2) from DBMS.   At one point, I transpose my dataset after proc compare and all the numeric field changed to character. Then in the end, due to requirement, i need to merge the transposed table with the auditlog dataset to ensure the "before" and "after" value are match. So that's why I need to change the numeric to character in auditlog dataset.     /* 1. Proc compare */ ods select comparesummary; ods output comparesummary = comparesummary; Proc compare base=work.extract_append_source_SDIS6 compare=work.extract_mart_SDIS6 out=work.field_compare_SDIS6 (compress=yes) outbase outcomp outdiff outnoequal ; id app_sys_code fi_code master_account_no sub_account_no; attrib _all_ label=""; run; ods select all; /* 2. proc transpose */ proc transpose data= work.field_compare_SDIS6 out= work.field_compare_SDIS6_transpose (rename = _name_ = Variable rename = Col1 = Base rename = Col2 = Compare rename = Col3 = Diff compress=yes ); by app_sys_code fi_code master_account_no sub_account_no ; var facility_type -- LUCountrycode; run; /* 3. after series of data cleaning */ data merge_compare_audit_SDIS6 (compress=yes); merge work.Final_Field_Compare_SDIS6 (in=A) work.AuditLog_Mart_SDIS6_clean (in=B keep= app_sys_code fi_code master_account_no sub_account_no variable from_numeric_value_c to_numeric_value_c from_character_value to_character_value update_source ); by app_sys_code fi_code master_account_no sub_account_no variable; /* Output all records (full join)*/ if A or B; CCRIS_N_Source = A; AuditLog = B; /* Matching tag */ length tag $10.; if variable in(&SDIS6_Char_Var) and base = from_character_value and compare = to_character_value then tag = "Match"; else if variable in(&SDIS6_Num_Var) and base = from_numeric_value_c and compare = to_numeric_value_c then tag = "Match"; else tag = "To Check"; run;     Now to think about it again, maybe i can just merge 3 tables together instead of doing the proc compare then transpose.. but I almost complete the program and i get what i want too.    ... View more

Thanks bro, you are right, i suppose to use [to_numeric_value] instead of [from_numeric_value]. I was struggling for an hour thinking is it i used the wrong format. I've tried many different format like w.d , bestw. , bestxw.d and many....   btw i also changed the format to 20.2 because i want it to be 2 decimal. 20.6 is just for trial and error. ... View more

hi @Patrick, the condition is validated true. At the beginning, i thought of this issue also (condition = false), that's why i create a column with variable name call [deci_tag].   btw, i found the reason why my code never works, that's because i used the wrong variable: to_numeric_value_c = strip(ifc(missing(from_numeric_value),"",put(from_numeric_value,20.0))); supposedly is the [to_numeric_value] instead of [from_numeric_value].   and also thanks for letting me know the system options missing and the length statement for ifc(), i never thought the lenght would be 200. I will go check about it with proc content. ... View more

hmmm, i run the code in SAS EG and it shows 9.04.0M5P091317. I believe i'm using the latest SAS version 9.4. However, it's probably due to my Enterprise Guide version is old (SAS EG 7.1), becuase i run the same code (proc freq) in Base SAS, the report is looking fine and neat. ... View more

hmmm. i suspect of that too. However, i didn't write and submit that code. Probably it is by default. ... View more

my code just simple as this proc freq data = merge_compare_audit_SDIS1 ; tables variable*app_sys_code ; where tag <> "Match"; run;   ... View more

i see, thanks for the explanation ... View more

Thanks for the help. However, why I cant just put a column name like "Missing in source" ?   This is because i may have other column name "Missing in CCRIS Mart". I wish to write using a column name rather than column number.     ... View more

Ahh, i got it now, all i want just a text and that semi colon is unnecessary. ... View more