BookmarkSubscribeRSS Feed
deleted_user
Not applicable
hi,

i got a question for one of my queries. This is the structure of my table:

table 1

customer_ID|event|date
1 | A | 20020529
1 | B | 20050403
1 |C | 20040509
2 |C | 20040509
2| B | 20050403
2| A | 20020529

Regarding this dataset my query should only contain the following records:
1|B| 20050403
2| B | 20050403

What might be the settings for the Query-/Filter Designer?

Regards
Chris
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Your data example appears to show, for your query steps, the resulting output file will have each unique CUSTOMER_ID, sorted by the DATE (variable / column) in descending order,and output variables CUSTOMER_ID, EVENT and DATE.

Scott Barry
SBBWorks, Inc.
Cynthia_sas
SAS Super FREQ
Hi:
In addition to Scott's comments, it also looks like your criteria for selection might have something to do either with EVENT or DATE -- what your desire output has in common is that the EVENT for both rows is equal to 'B' and the date for both rows is equal to 20050403.

You are the person who is most familiar with your data. The query that you build will need to use a filter to select the rows that you want. When you build the filter, you have to decide which criteria will get you the rows you want. For example:

Option 1: Filter and Select ALL the rows where event = 'B' and date = '20050403'
OR
Option 2: Filter and Select ALL the rows where event = 'B' (could get ANY date)
OR
Option 3: Filter and Select ALL the rows where date = '20050403' (could get ANY event code)
OR
Option 4: Filter and Select the rows where event = 'B' or date = '20050403'

cynthia
deleted_user
Not applicable
Thanks for your comments, but i think i haven't describe my problem exactly, so i try again:

the output should contain that row with the youngest date for every customer

chris Message was edited by: Chris55
LinusH
Tourmaline | Level 20
If you want to solve this in Filter and query, you need to create a computed column (max(date)), group by customer_id, and then subset via having (data = maxdate).

/Linus
Data never sleeps
deleted_user
Not applicable
hi linus,

thanks for your reply.
i have implemented that query but i am afraid it doesn't work.
Maybe the format (01012000) of the date-column is the problem?

chris
deleted_user
Not applicable
it works ... thank you.

seems my first version had some problems with the having statement!

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 668 views
  • 0 likes
  • 4 in conversation