topic Re: how to implement qualify statement in SAS DI? in SAS Data Management

how to implement qualify statement in SAS DI?

Sandhyaa — Tue, 08 Jan 2013 09:57:37 GMT

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?

Re: how to implement qualify statement in SAS DI?

LinusH — Tue, 08 Jan 2013 10:17:06 GMT

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.

Re: how to implement qualify statement in SAS DI?

Sandhyaa — Tue, 08 Jan 2013 10:55:59 GMT

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.

Re: how to implement qualify statement in SAS DI?

LinusH — Tue, 08 Jan 2013 11:34:14 GMT

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.

Re: how to implement qualify statement in SAS DI?

Sandhyaa — Thu, 10 Jan 2013 09:18:22 GMT

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?

Re: how to implement qualify statement in SAS DI?

LinusH — Thu, 10 Jan 2013 10:14:29 GMT

What do you need group by for?

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

Re: how to implement qualify statement in SAS DI?

Sandhyaa — Thu, 10 Jan 2013 11:03:22 GMT

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.

Re: how to implement qualify statement in SAS DI?

LinusH — Thu, 10 Jan 2013 13:07:25 GMT

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

Re: how to implement qualify statement in SAS DI?

Sandhyaa — Fri, 11 Jan 2013 09:15:30 GMT

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?

Re: how to implement qualify statement in SAS DI?

LinusH — Fri, 11 Jan 2013 09:44:29 GMT

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

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

Re: how to implement qualify statement in SAS DI?

Sandhyaa — Tue, 15 Jan 2013 09:17:17 GMT

Thanks Linush. Its working fine.