topic Re: translating a Microsoft Access SQL query to A SAS Proc SQL query in SAS Programming

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

joseatmc — Thu, 27 Jul 2017 14:05:25 GMT

SELECT CUSTOMER_NUMBER, BUSLINE_GROUPING, IIf([BUSLINE_GROUPING]="Knees",1,[ALT_LEVEL2_DESC]) AS 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 [C-Expanded PVM from Sushant] GROUP BY CUSTOMER_NUMBER, BUSLINE_GROUPING, IIf([BUSLINE_GROUPING]="Knees",1,[ALT_LEVEL2_DESC]), ALT_LEVEL2_DESC, ALT_LEVEL1_DESC, BUSLINE, MATERIAL_NUMBER HAVING (((ALT_LEVEL2_DESC)="HIP HEADS" Or (ALT_LEVEL2_DESC)="HIP LINER" Or (ALT_LEVEL2_DESC)="HIP SHELLS" Or (ALT_LEVEL2_DESC)="HIP STEMS" Or (ALT_LEVEL2_DESC)="KNEE FEMORAL") AND ((ALT_LEVEL1_DESC) Not Like "ZUK *") AND ((BUSLINE)<>"4545") AND ((MATERIAL_NUMBER) Not Like "719*" And ([C-Expanded MATERIAL_NUMBER) Not Like "7170*")) OR (((ALT_LEVEL2_DESC)="HIP HEADS" Or (ALT_LEVEL2_DESC)="HIP LINER" Or (ALT_LEVEL2_DESC)="HIP SHELLS" Or (ALT_LEVEL2_DESC)="HIP STEMS" Or (ALT_LEVEL2_DESC)="KNEE FEMORAL") AND ((ALT_LEVEL1_DESC) Not Like "ZUK *") AND ((BUSLINE)<>"4545") AND ((MATERIAL_NUMBER) Not Like "719*" And (MATERIAL_NUMBER) Not Like "7170*")) OR (((ALT_LEVEL2_DESC)="KNEE REVISION") AND ((ALT_LEVEL1_DESC) Like "*fem*") AND ((MATERIAL_NUMBER) Not Like "719*" And (MATERIAL_NUMBER) Not Like "7170*")) OR (((ALT_LEVEL2_DESC)="KNEE REVISION") AND ((ALT_LEVEL1_DESC) Like "*fem*") AND ((MATERIAL_NUMBER) Not Like "719*" And (MATERIAL_NUMBER) Not Like "7170*"));

The above SQL query is in an Access database. I'm trying to translate it in SAS and am having an awful time. I don't have a lot of experience with Access but I did translate this to the below SAS query. however this query doesn't create the exact same export as the Access query. It comes close, but doesn't exactly fit. There may be a situation where there are duplicate rows in the access query, but i'm not sure yet. (yes the IIF statement in the Access query is not a typo. it's exactly what came out of the view SQL part of the Access database. I thought it was a typo on my part, but it isn't.)

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 MATERIAL_NUMBER NOT LIKE "719%"

AND MATERIAL_NUMBER NOT LIKE "7170%"

AND (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%"))

OR (Alt_level2_Desc in ("KNEE REVISION") AND ALT_LEVEL1_DESC LIKE ("%FEM%"))

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

;quit;

I know this isn't much to go by, but I was trying to see if anyone could tell me what i'm doing wrong.

thanks,

Jose

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

AlanC — Thu, 27 Jul 2017 14:18:04 GMT

Hard to say w/o some testing on the real data. I would start with making sure the comparisons are correct. For example:

BUSLINE NOT IN ("4545")

Are you matching correctly here? For example, what if BUSLINE is " 4545" or "4545 " ? Strip/trim as needed to get a valid match. Same goes for any other string field you are working with.

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

joseatmc — Thu, 27 Jul 2017 14:57:59 GMT

Thanks for the quick response.. BUSLINE is a character field, but its really a 4 digit number.

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

AlanC — Thu, 27 Jul 2017 15:04:13 GMT

Well, it is not a number unless held as such in Access. In Access, is it defined as a number or a string? If string, it needs to be trimmed/stripped before a comparison to be safe. See how it is held as the column type and then trim/strip if it is a string/char.

It may/may not be the issue but it would be where I would start.

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

Reeza — Thu, 27 Jul 2017 15:04:42 GMT

IFF are CASE statements, or similar to an IF condition in Excel

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

Shmuel — Thu, 27 Jul 2017 15:05:56 GMT

No have I experience with access sql. Anyway I have some note:

1) You have not translate line:

IIf([BUSLINE_GROUPING]="Knees",1,[ALT_LEVEL2_DESC]) AS GrwthALT_LEVEL2_DESC,

which, probably, be translated into CASE, WHEN statement

2) You have the IIF statement within the GROUP BY statement.

I don't see that you relate to it and I don't know is it possible in sas sql

3) Check your code if you have all values in IN statement as in the qccess sql ( var=value1 or var=value2 ..)

4) Check carefully the parentheses especially when using OR / AND

5) here is the access sql in idented format to make more readable and make it easier to other to help you:

SELECT 
   CUSTOMER_NUMBER, 
   BUSLINE_GROUPING, 
   IIf([BUSLINE_GROUPING]="Knees",1,[ALT_LEVEL2_DESC]) AS 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 [C-Expanded PVM from Sushant] 
   GROUP BY CUSTOMER_NUMBER, BUSLINE_GROUPING, 
            IIf([BUSLINE_GROUPING]="Knees",1,[ALT_LEVEL2_DESC]), 
			ALT_LEVEL2_DESC, ALT_LEVEL1_DESC, BUSLINE, MATERIAL_NUMBER 
   HAVING (((ALT_LEVEL2_DESC)="HIP HEADS" Or 
          (ALT_LEVEL2_DESC)="HIP LINER" Or 
		  (ALT_LEVEL2_DESC)="HIP SHELLS" Or 
		  (ALT_LEVEL2_DESC)="HIP STEMS" Or 
		  (ALT_LEVEL2_DESC)="KNEE FEMORAL") AND 
		  ((ALT_LEVEL1_DESC) Not Like "ZUK *") AND 
		  ((BUSLINE)<>"4545") AND 
		  ((MATERIAL_NUMBER) Not Like "719*" And 
		  ([C-Expanded MATERIAL_NUMBER) Not Like "7170*")) OR 
		  (((ALT_LEVEL2_DESC)="HIP HEADS" Or 
		    (ALT_LEVEL2_DESC)="HIP LINER" Or 
		    (ALT_LEVEL2_DESC)="HIP SHELLS" Or 
		    (ALT_LEVEL2_DESC)="HIP STEMS" Or 
		    (ALT_LEVEL2_DESC)="KNEE FEMORAL") AND 
		   ((ALT_LEVEL1_DESC) Not Like "ZUK *") AND 
		  ((BUSLINE)<>"4545") AND 
		  ((MATERIAL_NUMBER) Not Like "719*" And 
		   (MATERIAL_NUMBER) Not Like "7170*")) OR 
		  (((ALT_LEVEL2_DESC)="KNEE REVISION") AND 
		   ((ALT_LEVEL1_DESC) Like "*fem*") AND 
		   ((MATERIAL_NUMBER) Not Like "719*" And 
		   (MATERIAL_NUMBER) Not Like "7170*")) OR 
		 (((ALT_LEVEL2_DESC)="KNEE REVISION") AND 
		  ((ALT_LEVEL1_DESC) Like "*fem*") AND 
		  ((MATERIAL_NUMBER) Not Like "719*" And 
		  (MATERIAL_NUMBER) Not Like "7170*"))
    ;

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

Reeza — Thu, 27 Jul 2017 15:10:38 GMT

Your 'HAVING' doesn't deal with aggregates so it likely needs to be WHERE, not HAVING

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

joseatmc — Thu, 27 Jul 2017 15:28:04 GMT

sorry I forgot to add a datastep that I did earlier in the code.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 MATERIAL_NUMBER NOT LIKE "719%"
AND MATERIAL_NUMBER NOT LIKE "7170%"
AND (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 %"))
OR (Alt_level2_Desc in ("KNEE REVISION") AND ALT_LEVEL1_DESC LIKE ("%FEM%"))
group by CUSTOMER_NUMBER,BUSLINE_GROUPING,GrwthALT_LEVEL2_DESC,ALT_LEVEL2_DESC,ALT_LEVEL1_DESC,BUSLINE,MATERIAL_NUMBER
;quit;

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

joseatmc — Thu, 27 Jul 2017 15:30:54 GMT

from what I can tell that IIF statement is basically saying if the BUSLINE GROUPING is equal to "KNEES" then GrwthALT_LEVEL2_DESC is equal to "1", otherwise GrwthALT_LEVEL2_DESC is equal to ALT_LEVEL2_DESC. that's what i did in the data step prior to the PROC SQL.

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

joseatmc — Thu, 27 Jul 2017 15:34:10 GMT

the HAVING statement is where i'm having trouble with to me it seems that it's repeating the exact same thing twice... for "HIP HEADS" and "HIP LINERS" and "HIP SHELLS" and "HIP STEMS" and "KNEE FEMORAL".. isn't the double statement redundant?

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

Reeza — Thu, 27 Jul 2017 15:36:58 GMT

That's where the brackets are important. I think the AND/OR connects them but they are different conditions.

Format your code using the brackets so you can identify the filters. Put it into logic and then convert that logic into SAS code, for example you can use IN instead of multiple ORs

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

Tom — Thu, 27 Jul 2017 16:37:27 GMT

First thing is learn to use the forum editor to include code so that it is not re-flowed as if it was normal paragraphs. Use the Insert SAS Code icon to paste in the code. Second format that code. I pasted your code to this free site https://sqlformat.org and it did a pretty good job. Only thing I had to do was move the commas and semi-colon from the end of the lines to the beginning of the next line where human eyes can see them.

SELECT CUSTOMER_NUMBER
     , BUSLINE_GROUPING
     , IIf([BUSLINE_GROUPING]="Knees",1,[ALT_LEVEL2_DESC]) AS 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 [C-Expanded PVM from Sushant]
GROUP BY CUSTOMER_NUMBER 
       , BUSLINE_GROUPING 
       , IIf([BUSLINE_GROUPING]="Knees",1,[ALT_LEVEL2_DESC])
       , ALT_LEVEL2_DESC
       , ALT_LEVEL1_DESC 
       , BUSLINE 
       , MATERIAL_NUMBER
HAVING (((ALT_LEVEL2_DESC)="HIP HEADS"
         OR (ALT_LEVEL2_DESC)="HIP LINER"
         OR (ALT_LEVEL2_DESC)="HIP SHELLS"
         OR (ALT_LEVEL2_DESC)="HIP STEMS"
         OR (ALT_LEVEL2_DESC)="KNEE FEMORAL")
        AND ((ALT_LEVEL1_DESC) NOT LIKE "ZUK *")
        AND ((BUSLINE)<>"4545")
        AND ((MATERIAL_NUMBER) NOT LIKE "719*"
             AND ([C-Expanded MATERIAL_NUMBER) NOT LIKE "7170*"))
OR (((ALT_LEVEL2_DESC)="HIP HEADS"
     OR (ALT_LEVEL2_DESC)="HIP LINER"
     OR (ALT_LEVEL2_DESC)="HIP SHELLS"
     OR (ALT_LEVEL2_DESC)="HIP STEMS"
     OR (ALT_LEVEL2_DESC)="KNEE FEMORAL")
    AND ((ALT_LEVEL1_DESC) NOT LIKE "ZUK *")
    AND ((BUSLINE)<>"4545")
    AND ((MATERIAL_NUMBER) NOT LIKE "719*"
         AND (MATERIAL_NUMBER) NOT LIKE "7170*"))
OR (((ALT_LEVEL2_DESC)="KNEE REVISION")
    AND ((ALT_LEVEL1_DESC) LIKE "*fem*")
    AND ((MATERIAL_NUMBER) NOT LIKE "719*"
         AND (MATERIAL_NUMBER) NOT LIKE "7170*"))
OR (((ALT_LEVEL2_DESC)="KNEE REVISION")
    AND ((ALT_LEVEL1_DESC) LIKE "*fem*")
    AND ((MATERIAL_NUMBER) NOT LIKE "719*"
         AND (MATERIAL_NUMBER) NOT LIKE "7170*"))
;

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*"))

Convert the IFF() function to something SAS can use.
- You could convert to normal SQL. Make sure both values are using the same type! I doubt that ALT_LEVEL2_DESC is a numeric variable.

     , case when (BUSLINE_GROUPING="Knees") then '1'
            else ALT_LEVEL2_DESC 
       end AS GrwthALT_LEVEL2_DESC

or use the SAS function IFC()

     , ifc(BUSLINE_GROUPING="Knees",'1',ALT_LEVEL2_DESC) AS GrwthALT_LEVEL2_DESC

There is another one in the GROUP BY, but it looks like the same thing as before, so use the CALCULATED keyword to just refer to the value you already calculated in this SELECT statement. Note no need for CALCULATED keyword if you have already created this variable in an extra step that it is just a normal reference to a variable coming form the input dataset and not a reference to something that was derived in this SELECT statement.

GROUP BY CUSTOMER_NUMBER 
       , BUSLINE_GROUPING 
       , calculated GrwthALT_LEVEL2_DESC
       , ALT_LEVEL2_DESC
       , ALT_LEVEL1_DESC 
       , BUSLINE 
       , MATERIAL_NUMBER

Also check the variables you reference in other places and make sure that they are not references to varaibles created in this SELECT clause, but are the names of variables coming from the source table. If they are then you will need to add the CALCULATED keyword there also.

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 %')

Personally I never use <> as an operator as it has two totally different meanings in SAS depending on whether you use it in normal SAS code or in SQL code (or WHERE statements). But it should work the same in PROC SQL as it works in other SQL versions.

Finally HAVING is applied after the observations have been calculated and WHERE applies to the data on the way into the SQL process. There is a big difference when you are using GROUP BY. It looks to me like you are referencing variables like BUSLINE that are not part of the data being output. So you probably want to convert this HAVING clause to a WHERE clause.

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

joseatmc — Thu, 27 Jul 2017 17:00:19 GMT

So apparently it's my Not like statements... for some reason in my sas query i'm saying that MATERIAL NUMBER is Not like "719%" and MATERIAL NUMBER is Not LIKE "7170%" . the out put excludes all material numbers with 7170 but keeps the 719 numbers... What would be the syntax to combinde the statements? i.e. something like " MATERIAL_NUMBER not like ("719%" ,"7170%")... that doesn't work btw. does anyone know how to write that?

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

Reeza — Thu, 27 Jul 2017 17:03:25 GMT

You need to simplify your logic.

For your not like issue, I would recommend using single quotes, so it doesn't try and resolve to a macro, but I would also recommend creating a new variable that is the first three characters and test that alone. You have multiple conditions that you'll likely be able to collapse when you do this.

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

Tom — Thu, 27 Jul 2017 17:04:25 GMT

@joseatmc wrote:

So apparently it's my Not like statements... for some reason in my sas query i'm saying that MATERIAL NUMBER is Not like "719%" and MATERIAL NUMBER is Not LIKE "7170%" . the out put excludes all material numbers with 7170 but keeps the 719 numbers... What would be the syntax to combinde the statements? i.e. something like " MATERIAL_NUMBER not like ("719%" ,"7170%")... that doesn't work btw. does anyone know how to write that?

MATERIAL_NUMBER should be a character variable. Perhaps your '719%' values are really ' 719%' values.

Try testing STRIP(MATERIAL_NUMBER) instead.

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

joseatmc — Thu, 27 Jul 2017 17:10:55 GMT

@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.

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

joseatmc — Thu, 27 Jul 2017 18:19:19 GMT

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;