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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

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

 


Accepted Solutions
Solution
4 weeks ago
Occasional Contributor
Posts: 11

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

[ Edited ]

 

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;

View solution in original post


All Replies
Contributor
Posts: 62

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

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.

 

 

 

Occasional Contributor
Posts: 11

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

[ Edited ]

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

Contributor
Posts: 62

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

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. 

Trusted Advisor
Posts: 1,400

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

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

 

Super User
Posts: 17,907

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

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

Super User
Posts: 17,907

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

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

Occasional Contributor
Posts: 11

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

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;
Occasional Contributor
Posts: 11

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

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.

 

 

 

Occasional Contributor
Posts: 11

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

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?

Super User
Posts: 17,907

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

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

Occasional Contributor
Posts: 11

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

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?

Super User
Posts: 17,907

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

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.

Super User
Super User
Posts: 6,502

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


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.

Super User
Super User
Posts: 6,502

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

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 284 views
  • 2 likes
  • 5 in conversation