topic Re: left join sql do not work in SAS Procedures

left join sql do not work

sasphd — Fri, 23 Sep 2022 17:52:17 GMT

Hello,

when I run the program below there is no error in the log but the variable EPU is empty "."


proc sql;
 create table test1 as
 select Distinct R.*, B.EPU
 from test as R
 left join  EPU_Data as B on R.sas_date=B.sas_date
order by R.Secid, R.sas_date;
 quit;

Re: left join sql do not work

PaigeMiller — Fri, 23 Sep 2022 18:02:24 GMT

This happens because one (or both) of the following is true:

Variable EPU is always missing data set B; or
variable SAS_DATE in data set A never matches the values of SAS_DATE in data set B

Re: left join sql do not work

sasphd — Fri, 23 Sep 2022 18:09:34 GMT

thanks for the response.
I have the same data format for sas_date variable and EPU is not missing there is a number for each data?

Re: left join sql do not work

PaigeMiller — Fri, 23 Sep 2022 18:42:15 GMT

@sasphd wrote:
I have the same data format for sas_date variable and EPU is not missing there is a number for each data?

Not the format ... the actual values of SAS_DATE don't match.

Re: left join sql do not work

Kurt_Bremser — Sat, 24 Sep 2022 00:19:12 GMT

Run this:

data test1,
merge
  test (in=t)
  epu_data (
    in=e
    keep=sas_date epu
  )
;
by sas_date,
if t and e;
run;

and then post the complete log (code and messages) from this step.

Re: left join sql do not work

PGStats — Sat, 24 Sep 2022 20:28:47 GMT

Maybe you imported the dates from somewhere where they are not represented quite the same as in SAS (e.g. Excel)? Happened to me once. The date numbers were not integers. Try:

left join EPU_Data as B on round(R.sas_date) = round(B.sas_date)