topic Re: How do i convert this ms access sql query to a sas query? in SAS Programming

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

joseatmc — Fri, 04 Aug 2017 15:03:55 GMT

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

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

kiranv_ — Fri, 04 Aug 2017 15:25:54 GMT

I am not very sure, but from what I have read it, it implies first record as enetered in MS access. Personally I feel this is tough to emulate. for example , say there are multiple values of sales_distict_code and they are 123, 421, 223. The first functions picks up the first value entered in MS access and this could be anything as it depends on entry of that value in MS access. A min or max function will help you achieve similar results by picking one value but might not be same result as in MS Access. I would try to use min or max function instead of first and then compare the results. Probably you may need to contact business user and ask, what is that he wants to see in output.

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

joseatmc — Fri, 04 Aug 2017 16:13:12 GMT

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?

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

Tom — Fri, 04 Aug 2017 16:22:58 GMT

You should be able to do that with PROC SUMMARY.

So you have three types of variables in that query. Grouping variables, Sums and "Firsts".

So here is a simple example using PROC SUMMARY.

data have ;
  input group $ group_code amount ;
cards;
A 1 10
A 1 10
A 2 10
B 3 5
B 3 5
;

%let group= group;
%let sum= amount;
%let first=group_code;
proc summary nway data=have  ;
  class &group ;
  var &sum ;
  output out=want(drop=_type_ _freq_)
    idgroup( out[1] (&first) = ) 
    sum=
  ;
run;

                group_
Obs    group     code     amount

 1       A         1        30
 2       B         3        10

So basically for this to work for your data you need to supply the space delimited liss of variable names into the macro variables and point to the source table.

%let group= CUSTOMER_NUMBER CUSTOMER_NAME ZIP5_CODE ....;
%let sum=YTDPYNET_QTY YTDPYNET_SALES ...;
%let first=SALES_DISTRICT_CODE SALES_DISTRICT_DESC ....;

I am not sure if you need to add a FORMAT statement to handle the values of STPWeightedGrwth or if you will first need to round it to replicate how Access is handleing that FORMAT() function call.

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

joseatmc — Fri, 04 Aug 2017 16:23:52 GMT

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!!

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

RW9 — Fri, 04 Aug 2017 16:26:36 GMT

From the net:

https://msdn.microsoft.com/en-us/library/bb177902(v=office.12).aspx

"Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary."

So the query you present might work if all the values within the group by are the same, otherwise it is just picking the first one from a random order. Pull the data out as is from the access database, assign a numer to each record to show how it is in the access db, then use that number in your query.

Or better yet, find out what the intention of the query was, and build it specifically in SAS in a logical and repeatable manner. For instance that vast amount of code you present could be replaced by one proc means, by group being the group by variables.