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
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;
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.
Thanks for the quick response.. BUSLINE is a character field, but its really a 4 digit number.
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.
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*"))
;
IFF are CASE statements, or similar to an IF condition in Excel
Your 'HAVING' doesn't deal with aggregates so it likely needs to be WHERE, not HAVING
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.
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?
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
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?
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.
@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.
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.
AND ('C-Expanded MATERIAL_NUMBER'n NOT LIKE "7170*"))
, 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.
AND ((ALT_LEVEL1_DESC) NOT LIKE 'ZUK %')
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.