how to implement qualify statement in SAS DI?

[Sandhyaa]

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?

[LinusH]

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.

[Sandhyaa]

The requirement is like this.

Col_1     Col_2

123          Abc

123          Bcd          

234          Abc

234          Bcd

And i want the output of reords like this

Col_1     Col_2

123          Abc

234          Abc

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.

[LinusH]

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.

[Sandhyaa]

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?

[LinusH]

What do you need group by for?

Be clear on why you can't use SAS sort.

[Sandhyaa]

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.

[LinusH]

Then you need to pre-sort the data, before the SAS sort step.

[Sandhyaa]

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?

[LinusH]

Step one: Sort your data both according to your group and subgroup.

Step two: Sort by your group, and select option NODUPKEY.

[Sandhyaa]

Thanks Linush. Its working fine.