When I run the following code I get a table, but one missing customers, because they are not in one of the tables.
proc SQL;
Create table work.firstep
as select distinct t1.*
, "VR" || substr(cats ("0000000", put(CUSProfile_AN,7.)),length (strip (cats ("0000000", put(CUSProfile_AN,7.))))-6,7) as VRID
from sasuser.test1 t1
join rimsrpt.rptcasesummary t2
on t1.'SSN Without Dashes'n = t2.customerid
where t2.extractdate ='31aug2017'd;
quit;
So I try left join to test where the extra cases are. And I get no table at all. Any suggestions what could cause this (it makes no sense to me at all).
This is the left join I run.
proc SQL;
Create table work.firstep
as select distinct t1.*
, "VR" || substr(cats ("0000000", put(CUSProfile_AN,7.)),length (strip (cats ("0000000", put(CUSProfile_AN,7.))))-6,7) as VRID
from sasuser.test1 t1
left join rimsrpt.rptcasesummary t2
on t1.'SSN Without Dashes'n = t2.customerid
where t2.extractdate ='31aug2017'd;
quit;
proc SQL;
Create table work.firstep
as select distinct t1.*
, "VR" || substr(cats ("0000000", put(CUSProfile_AN,7.)),length (strip (cats ("0000000", put(CUSProfile_AN,7.))))-6,7) as VRID
from sasuser.test1 t1
join rimsrpt.rptcasesummary t2
on t1.'SSN Without Dashes'n = t2.customerid
where t2.extractdate ='31aug2017'd;
quit;
So I try left join to test where the extra cases are. And I get no table at all. Any suggestions what could cause this (it makes no sense to me at all).
This is the left join I run.
proc SQL;
Create table work.firstep
as select distinct t1.*
, "VR" || substr(cats ("0000000", put(CUSProfile_AN,7.)),length (strip (cats ("0000000", put(CUSProfile_AN,7.))))-6,7) as VRID
from sasuser.test1 t1
left join rimsrpt.rptcasesummary t2
on t1.'SSN Without Dashes'n = t2.customerid
where t2.extractdate ='31aug2017'd;
quit;