translating a Microsoft Access SQL query to A SAS Proc SQL query

[joseatmc]

---

@Tom wrote:

You need to fix the SQL/Server specfic stuff.

• Remove the square brackets [] around variable and dataset names.  If you are using VALIDVARNAME=ANY and/or VALIDMEMNAME=EXTEND then you will need format them as name literals. Otherwise use the real name and not the ones with spaces in them.
  • This looks like a typo, there is a missing right bracket.  Was that in your original code?  Does that mess up the nesting of the () in the HAVING clause?

             AND ('C-Expanded MATERIAL_NUMBER'n NOT LIKE "7170*"))

---

Yes the Access query  referenced a table called C-Expanded PVM from Sushant in every  field trying to be returned ... i removed all of them but missed one. Sorry about that.

---

@Tom wrote:

• What is with the * in the LIKE strings? Does SQL Server use * as wildcard instead of %?  Or do the values really contain the *, but if so then why is it using LIKE instead of just =?

    AND ((ALT_LEVEL1_DESC) NOT LIKE 'ZUK %')

Yes the * in the Access queery is the "wild card" like the % is SAS.

[joseatmc · Accepted solution]

here is the translation!!  finally figured it out!

data ORTHO_SALES_&FY._&PERIOD._ALT2v3;

FORMAT GrwthALT_LEVEL2_DESC $32.;

set ORTHO_SALES_&FY._&PERIOD._ALT2v2;

if BUSLINE_GROUPING = "KNEES" THEN GrwthALT_LEVEL2_DESC = "1" ;

else GrwthALT_LEVEL2_DESC = ALT_LEVEL2_DESC;

;run;

proc sql;

create table Custpvm_recon_Compnts_exclZUK as

select CUSTOMER_NUMBER,BUSLINE_GROUPING,GrwthALT_LEVEL2_DESC,SUM(YTDCYNET_QTY) AS SumOfYTDCYNET_QTY,

SUM(YTDPYNET_QTY) AS SumOfYTDPYNET_QTY,SUM(YTDCYNET_SALES) AS SumOfYTDCYNET_SALES,SUM(YTDPYNET_SALES) AS SumOfYTDPYNET_SALES

FROM ORTHO_SALES_&FY._&PERIOD._ALT2v3

WHERE (ALT_LEVEL2_DESC IN ("HIP HEADS","HIP LINER","HIP SHELLS","HIP STEMS","KNEE FEMORAL")AND BUSLINE NOT in("4545")AND ALT_LEVEL1_DESC NOT LIKE ("ZUK %")and Material_number not like ("719%")and Material_number NOT LIKE ("7170%"))

OR (Alt_level2_Desc in ("KNEE REVISION") AND ALT_LEVEL1_DESC LIKE ("%FEM%") and Material_number not like ("719%")and Material_number NOT LIKE ("7170%"))

group by CUSTOMER_NUMBER,BUSLINE_GROUPING,GrwthALT_LEVEL2_DESC,ALT_LEVEL2_DESC,ALT_LEVEL1_DESC,BUSLINE,MATERIAL_NUMBER

;quit;