Want to set the timeid

[Sultana]

I performed these steps

Autoload 'F:\sasdata\data\autoload\report_mart_auto_load';
proc sql ;
create table test as
Select
 b.NAICSCode as NAICS_Code,
 b.callreportcode as Call_Code,
 b.Loannumber as Account_Number,
 b.Name as Account_Name,
 a.Currentprincipalbalance as Balance_Today,
 a.Lineavailable as Lineavailable,
 sum(a.Currentprincipalbalance,a.Lineavailable) as Commitment_Amount format=comma15.2,
 c.Officername,
 a.timeid
 
from Autoload.Fact_Loan_Balance a
left join Autoload.DIM_LOAN b on a.Accountkey=b.Accountkey
left join Autoload.DIM_OFFICER c on b.OfficerKey=c.Officerkey
left join Autoload.DIM_BRANCH_COST_CENTER d on b.branchcostcenterkey=d.branchcostcenterkey
where a.timeid='2018-07-31' and
    
quit;

I get the result

but when I run 

Autoload 'F:\sasdata\data\autoload\report_mart_auto_load';
proc sql ;
create table test as
Select
 b.NAICSCode as NAICS_Code,
 b.callreportcode as Call_Code,
 b.Loannumber as Account_Number,
 b.Name as Account_Name,
 a.Currentprincipalbalance as Balance_Today,
 a.Lineavailable as Lineavailable,
 sum(a.Currentprincipalbalance,a.Lineavailable) as Commitment_Amount format=comma15.2,
 c.Officername,
 a.timeid
 
from Autoload.Fact_Loan_Balance a
left join Autoload.DIM_LOAN b on a.Accountkey=b.Accountkey
left join Autoload.DIM_OFFICER c on b.OfficerKey=c.Officerkey
left join Autoload.DIM_BRANCH_COST_CENTER d on b.branchcostcenterkey=d.branchcostcenterkey
where a.timeid='20180400'  and
   ;
quit;

I get an empty data set .

Please let me know since I need the data set with 20180400

[r_behata]

Try running a Proc freq on the timeid variable to see how the underlying data is . You may have to change the value in the where clause based on the format of the timeid (2018-04-00 may be ).

[VDD]

@Sultana wrote where a.timeid='20180400'  and

that is not a valid date

[Sultana]

Ok so what if I need it as 20180400


[image: image.png]


Now can u help me

[r_behata]

Cannot see the image, please try again. It would be more helpful if you could share the log as well.

[image: image.png]

[Reeza]

Your query ends with AND?
Fix that or include the whole query and log.

What does your TIMEID variable look like, what is the format and type? That defines how you need to use it and you're likely not doing that part correctly at the moment.

[SuryaKiran]

If your query didn't get any result than there might not be any records in the table with that particular value(a.timeid='20180400')

Make sure that the tables have the values exactly how your giving them.