I am adding data from table 1 to table 2 but it is not showing results in table 2.

[Sandeep77]

Hello Experts,

I have created a table to get all accounts in trace. Once I got that information, I am adding Lima trace information to the table. But when I add the additional code, All_Lima_Trace table does not show any information. Can you please check and suggest what is the mistake I am doing here?

*GETS ALL THE ACCOUNTS IN TRACE */

proc sql;
create table Accs_for_LIMA as 
	 select CustomerCode as debt_code,
			PersonTitle as Title,
			PersonForename as First_Name,
			PersonSurname as Surname,
			Addressline1,
			Addressline2,
			Addressline3,
			Addressline4,
			Addressline5,
			AddressPostcode,
			PersonDateOfBirth as DOB
		from store.TU_Format;
quit;

/*GETS ALL THE LIMA TRACES FOR THE ACCOUNTS IN TRACE */

proc sql;
create table All_LIMA_Traces as
	select	t1.tcfAccountNumber as Debt_Code,
			t1.tcfClientCode as Client_Code,
			t1.tcfPostCode as Postcode_Traced_From,
			t1.tcfNewAddressLine1 as Traced_Add1,
			t1.tcfNewAddressLine2 as Traced_Add2,
			t1.tcfNewAddressLine3 as Traced_Add3,
			t1.tcfNewAddressLine4 as Traced_Add4,
			t1.tcfNewPostCode as Traced_Postcode,
			t1.tcfTraceResultType as LIMA_Result_Code,
			t2.trtDescription as LIMA_Result_Type,
			t1.tcfLivingasStated as LAS_Flag,
			t1.tcfTimeStamp as Time_Stamp,
			t1.tcfDateConfirmed as Trace_Confirmed
		from DBTrace.tCaseFlowDataAudit as t1
			left join DBTrace.tResultType as t2 on (t1.tcfTraceResultType = t2.trtTypeID and t2.trtTraceSource ='LIMA')
				where t1.tcfAccountNumber in (select debt_code from Accs_for_LIMA) and t1.tcfTimeStamp >= t1.tcfDateConfirmed and t1.tcfTimeStamp > '27SEP2022:00:00:00.000'dt;
quit;

LOG INFO:

NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28         


29         proc sql;
30         create table All_LIMA_Traces as
31         	select	t1.tcfAccountNumber as Debt_Code,
32         			t1.tcfClientCode as Client_Code,
33         			t1.tcfPostCode as Postcode_Traced_From,
34         			t1.tcfNewAddressLine1 as Traced_Add1,
35         			t1.tcfNewAddressLine2 as Traced_Add2,
36         			t1.tcfNewAddressLine3 as Traced_Add3,
37         			t1.tcfNewAddressLine4 as Traced_Add4,
38         			t1.tcfNewPostCode as Traced_Postcode,
39         			t1.tcfTraceResultType as LIMA_Result_Code,
40         			t2.trtDescription as LIMA_Result_Type,
41         			t1.tcfLivingasStated as LAS_Flag,
42         			t1.tcfTimeStamp as Time_Stamp,
43         			t1.tcfDateConfirmed as Trace_Confirmed
44         		from DBTrace.tCaseFlowDataAudit as t1
45         			left join DBTrace.tResultType as t2 on (t1.tcfTraceResultType = t2.trtTypeID and t2.trtTraceSource ='LIMA')
46         				where t1.tcfAccountNumber in (select debt_code from Accs_for_LIMA) and t1.tcfTimeStamp >= t1.tcfDateConfirmed and
46       ! t1.tcfTimeStamp > '27SEP2022:00:00:00.000'dt;
NOTE: Table WORK.ALL_LIMA_TRACES created, with 0 rows and 13 columns.

47         quit;

[Quentin]

You have a WHERE clause in your query. Are you sure there are records that satisfy your query? Maybe try removing the WHERE clause to check.

[ballardw]

A generic comment about comments in code.

Your have the comments before two sql blocks.

/*GETS ALL THE ACCOUNTS IN TRACE */

/*GETS ALL THE LIMA TRACES FOR THE ACCOUNTS IN TRACE */

 When I read those comments I expected at least one of these statements to be true :

1) a data set named Trace as a source

2) a common data set used as a source

So as a comment for a person just getting that code the bit about "the accounts in trace" is a bit confusing as to to what is actually intended.