BookmarkSubscribeRSS Feed
Sandhyaa
Calcite | Level 5

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?

10 REPLIES 10
LinusH
Tourmaline | Level 20

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
Sandhyaa
Calcite | Level 5

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
Tourmaline | Level 20

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
Sandhyaa
Calcite | Level 5

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
Tourmaline | Level 20

What do you need group by for?

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

Data never sleeps
Sandhyaa
Calcite | Level 5

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
Tourmaline | Level 20

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

Data never sleeps
Sandhyaa
Calcite | Level 5

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
Tourmaline | Level 20

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
Sandhyaa
Calcite | Level 5

Thanks Linush. Its working fine.

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 7806 views
  • 3 likes
  • 2 in conversation