01-08-2013 04:57 AM
We have a requirement to group the records based on a field and extract only one record from each group.
Kind of using statements like,
qualify(row number()over(partition by order by)).
How to implement this in SAS DI over SAS Datasets?
01-08-2013 05:17 AM
Not sure what you mean by "qualify", "over" etc.
Depending of the requirement, there are several ways to keep only one row per group variable value.
These includes SAS Sort, Distinct in Extract or Join, or using group By in Extract/Join.
01-08-2013 05:55 AM
The requirement is like this.
And i want the output of reords like this
Here i meant, partition_by Col_1, order_by Col_2 Asc, qualify the statments whose row_number =1
It typically means assigning a row number after the order by and partition by and fetch the records with row_number =1
I can find this function in SAS DI if the source is a Teradata table. But not sure of how to implement this for SAS Datasets.
01-08-2013 06:34 AM
If you just want to pick the first record for a group by value, then SAS sort is what you need, no need to assign temporary row number (unless you want to keep them for some other purpose).
If like/need to specify Teradata syntax, try to use explicit SQL pass-thru.
01-10-2013 04:18 AM
With sas sort transformation, we dont have a group by. I want to order the the fields within a particular group and i need one record from each group as the output. My source is SAS dataset. Not teradata.
So could you please tell us a possible solution to achieve this?
01-10-2013 06:03 AM
In the example i mentioned above, i want to group the columns based on Col_1 and want to sort within the group, so that i can pick the first record from each group. This is the requirment.
01-11-2013 04:15 AM
Linus, could you please me more clear. I wanna know how can we achieve this using a sort transformation alone? Is there any option in SORT transformation where we can sort within a group and filter only one record out of the group?
01-11-2013 04:44 AM
Step one: Sort your data both according to your group and subgroup.
Step two: Sort by your group, and select option NODUPKEY.