SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

how to implement qualify statement in SAS DI?

Reply
Contributor
Posts: 23

how to implement qualify statement in SAS DI?

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?

Super User
Posts: 5,441

Re: how to implement qualify statement in SAS DI?

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.

Data never sleeps
Contributor
Posts: 23

Re: how to implement qualify statement in SAS DI?

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.

Super User
Posts: 5,441

Re: how to implement qualify statement in SAS DI?

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.

Data never sleeps
Contributor
Posts: 23

Re: how to implement qualify statement in SAS DI?

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?


Super User
Posts: 5,441

Re: how to implement qualify statement in SAS DI?

What do you need group by for?

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

Data never sleeps
Contributor
Posts: 23

Re: how to implement qualify statement in SAS DI?

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.

Super User
Posts: 5,441

Re: how to implement qualify statement in SAS DI?

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

Data never sleeps
Contributor
Posts: 23

Re: how to implement qualify statement in SAS DI?

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?

Super User
Posts: 5,441

Re: how to implement qualify statement in SAS DI?

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

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

Data never sleeps
Contributor
Posts: 23

Re: how to implement qualify statement in SAS DI?

Thanks Linush. Its working fine.

Ask a Question
Discussion stats
  • 10 replies
  • 2592 views
  • 3 likes
  • 2 in conversation