cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
joseatmc
‎08-30-2017
  • 11 Posts
  • 2 Likes Given
  • 1 Solutions
  • 0 Likes Received

Re: How do i convert this ms access sql query to a sas query?

by Obsidian | Level 7 in SAS Programming
‎08-04-2017 12:23 PM
‎08-04-2017 12:23 PM
When I changed my query from first to min I got the exact out put as in the Access database. not sure if min can replace first in every scenario , but it did in this one. Thank you!! ... View more

Re: How do i convert this ms access sql query to a sas query?

by Obsidian | Level 7 in SAS Programming
‎08-04-2017 11:39 AM
‎08-04-2017 11:39 AM
Thanks for that, however, some of the fields are character for example  Sales_district_desc, and Sales_Group_desc, and Promo_desc and Pricing_type are all character fields... Sales_district_code is a number, as well as Sales group_code, and Promo.   how will min and max work with character fields?   ... View more

How do i convert this ms access sql query to a sas query?

by Obsidian | Level 7 in SAS Programming
‎08-04-2017 11:02 AM
‎08-04-2017 11:02 AM
    Can someone explain what the "First." functions in MS ACCESS are doing here and how can I convert this to a SAS query? is it just reomving duplicate data?  I'm not well versed in ms access.   Thanks for any help you can provide!   SELECT [2h CustPVM >109000].CUSTOMER_NUMBER, [2h CustPVM >109000].CUSTOMER_NAME, [2h CustPVM >109000].ZIP5_CODE, [2h CustPVM >109000].CITY, [2h CustPVM >109000].STATE, [2h CustPVM >109000].CBSA, First([2h CustPVM >109000].SALES_DISTRICT_CODE) AS SALES_DISTRICT_CODE, First([2h CustPVM >109000].SALES_DISTRICT_DESC) AS SALES_DISTRICT_DESC, First([2h CustPVM >109000].SALES_GROUP_CODE) AS SALES_GROUP_CODE, First([2h CustPVM >109000].SALES_GROUP_DESC) AS SALES_GROUP_DESC, [2h CustPVM >109000].CUSTOMER_GROUP1, [2h CustPVM >109000].CUSTOMER_GROUP1_DESC, [2h CustPVM >109000].IDN, [2h CustPVM >109000].CUSTOMER_GROUP2, [2h CustPVM >109000].CUSTOMER_GROUP2_DESC, [2h CustPVM >109000].[GPO Name], [2h CustPVM >109000].F400, [2h CustPVM >109000].MATERIAL_NUMBER, [2h CustPVM >109000].MATERIAL_DESC, [2h CustPVM >109000].LEVEL5_DESC_CATEGORY, [2h CustPVM >109000].LEVEL4_DESC_CLASS, [2h CustPVM >109000].BUSLINE, [2h CustPVM >109000].BUSLINE_DESC, [2h CustPVM >109000].BUSLINE_GROUPING_3, [2h CustPVM >109000].BUSLINE_GROUPING_2, [2h CustPVM >109000].BUSLINE_GROUPING, [2h CustPVM >109000].ORTHO_DIVISION, [2h CustPVM >109000].ALT_LEVEL1_DESC, [2h CustPVM >109000].ALT_LEVEL2_DESC, First([2h CustPVM >109000].PROMO) AS PROMO, First([2h CustPVM >109000].[Promo Description]) AS [Promo Description], First([2h CustPVM >109000].[Pricing Type]) AS [Pricing Type], Sum([2h CustPVM >109000].YTDPYNET_QTY) AS YTDPYNET_QTY, Sum([2h CustPVM >109000].YTDPYNET_SALES) AS YTDPYNET_SALES, Sum([2h CustPVM >109000].YTDPYGROSS_SALES) AS YTDPYGROSS_SALES, Sum([2h CustPVM >109000].YTDPYCOST_OF_SALES) AS YTDPYCOST_OF_SALES, Sum([2h CustPVM >109000].YTDCYNET_QTY) AS YTDCYNET_QTY, Sum([2h CustPVM >109000].YTDCYNET_SALES) AS YTDCYNET_SALES, Sum([2h CustPVM >109000].YTDCYGROSS_SALES) AS YTDCYGROSS_SALES, Sum([2h CustPVM >109000].YTDCYCOST_OF_SALES) AS YTDCYCOST_OF_SALES, Format([STPWeightedGrwth],"0.00000%") AS STPWeightedGrwthString FROM [2h CustPVM >109000] GROUP BY [2h CustPVM >109000].CUSTOMER_NUMBER, [2h CustPVM >109000].CUSTOMER_NAME, [2h CustPVM >109000].ZIP5_CODE, [2h CustPVM >109000].CITY, [2h CustPVM >109000].STATE, [2h CustPVM >109000].CBSA, [2h CustPVM >109000].CUSTOMER_GROUP1, [2h CustPVM >109000].CUSTOMER_GROUP1_DESC, [2h CustPVM >109000].IDN, [2h CustPVM >109000].CUSTOMER_GROUP2, [2h CustPVM >109000].CUSTOMER_GROUP2_DESC, [2h CustPVM >109000].[GPO Name], [2h CustPVM >109000].F400, [2h CustPVM >109000].MATERIAL_NUMBER, [2h CustPVM >109000].MATERIAL_DESC, [2h CustPVM >109000].LEVEL5_DESC_CATEGORY, [2h CustPVM >109000].LEVEL4_DESC_CLASS, [2h CustPVM >109000].BUSLINE, [2h CustPVM >109000].BUSLINE_DESC, [2h CustPVM >109000].BUSLINE_GROUPING_3, [2h CustPVM >109000].BUSLINE_GROUPING_2, [2h CustPVM >109000].BUSLINE_GROUPING, [2h CustPVM >109000].ORTHO_DIVISION, [2h CustPVM >109000].ALT_LEVEL1_DESC, [2h CustPVM >109000].ALT_LEVEL2_DESC, Format([STPWeightedGrwth],"0.00000%");   ... View more

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

by Obsidian | Level 7 in SAS Programming
‎07-27-2017 02:18 PM
‎07-27-2017 02:18 PM
  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 more

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

by Obsidian | Level 7 in SAS Programming
‎07-27-2017 01:10 PM
‎07-27-2017 01:10 PM
@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.         ... View more

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

by Obsidian | Level 7 in SAS Programming
‎07-27-2017 01:00 PM
‎07-27-2017 01:00 PM
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? ... View more

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

by Obsidian | Level 7 in SAS Programming
‎07-27-2017 11:34 AM
‎07-27-2017 11:34 AM
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? ... View more

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

by Obsidian | Level 7 in SAS Programming
‎07-27-2017 11:30 AM
‎07-27-2017 11:30 AM
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.       ... View more

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

by Obsidian | Level 7 in SAS Programming
‎07-27-2017 11:28 AM
‎07-27-2017 11:28 AM
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; ... View more

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

by Obsidian | Level 7 in SAS Programming
‎07-27-2017 10:57 AM
‎07-27-2017 10:57 AM
Thanks for the quick response.. BUSLINE  is a character field, but its really a 4 digit number. ... View more

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

by Obsidian | Level 7 in SAS Programming
‎07-27-2017 10:05 AM
‎07-27-2017 10:05 AM
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   ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎08-30-2017 04:01 PM
Likes given to
View All
Quick links