Conversion MS Access SQL to PROC SQL

[saspert]

Hi,

I have issues trying to convert an MS access sql query to PROC SQL query. Appreciate any suggestions.

UPDATE [6 Cases w All Customer Detail] LEFT JOIN [1 EDW Customer Detail by ECID] ON ([6 Cases w All Customer Detail].Cycle=[1 EDW Customer Detail by ECID].Cycle) AND ([6 Cases w All Customer Detail].[ECI Number]=[1 EDW Customer Detail by ECID].[Eci Nb]) SET [6 Cases w All Customer Detail].[Case Use] = "No", [6 Cases w All Customer Detail].[Account Use] = "No", [6 Cases w All Customer Detail].[Account Use Reason] = "No Account Found"

WHERE ((([6 Cases w All Customer Detail].[Account Use]) Is Null) AND (([6 Cases w All Customer Detail].Cycle) Like [Enter Cycle]) AND (([1 EDW Customer Detail by ECID].[Eci Nb]) Is Null));

I have tried 3 different ways to convert the above query but am not able to do so -

1. PROC SQL create table having a left join

2. DATA A; update b c; by field names; run

3. proc sql update a set fields=values where

Thanks,

saspert

[PGStats]

You can only update a single table with proc SQL. Bracketed names should be replaced with literal names. Something like

UPDATE X.'6 Cases w All Customer Detail'n as A

  SET 'Case Use'n = "No",

  'Account Use'n = "No",

  'Account Use Reason'n = "No Account Found"

WHERE

  'Account Use'n Is missing AND

  Cycle Like "Enter Cycle" AND

  'ECI Number'n not in

  (select  'Eci Nb'n

  from X.'1 EDW Customer Detail by ECID'n

  where Cycle = A.Cycle);

Might work, if X is the library where the tables reside.

If the update operation only involves DB tables, you might be better using your MS-Access query as a pass-through query.

PG

[saspert]

Hi PG,

Thanks for the tip. But why did you have the not in clause instead of the in clause. My attempt at this route has the  = clause because I need to join on the ECI Number to ECI Nb. But then it gives me errors because it is a 1 - many join.

So I will need to change the clause from = to not in?

[PGStats]

In a left join, all records from the left side table will be present but when they are not matched with records from the right side table, all variables from the right side table will be missing. Thus, I assumed that your condition [1 EDW Customer Detail by ECID].[Eci Nb]) Is Null was equivalent to [Eci Nb] being absent from table [1 EDW Customer Detail by ECID] v.

PG