@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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.