BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
joseatmc
Obsidian | Level 7

 

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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.

View solution in original post

5 REPLIES 5
kiranv_
Rhodochrosite | Level 12

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.

joseatmc
Obsidian | Level 7

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?

 

joseatmc
Obsidian | Level 7
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!!
Tom
Super User Tom
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 864 views
  • 4 likes
  • 4 in conversation