topic Re: Problems with query design in SAS Enterprise Guide

Problems with query design

deleted_user — Mon, 31 May 2010 13:47:23 GMT

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

Re: Problems with query design

sbb — Mon, 31 May 2010 14:56:17 GMT

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.

Re: Problems with query design

Cynthia_sas — Mon, 31 May 2010 16:21:08 GMT

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

Re: Problems with query design

deleted_user — Thu, 03 Jun 2010 11:37:40 GMT

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

Re: Problems with query design

LinusH — Fri, 04 Jun 2010 08:13:25 GMT

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

Re: Problems with query design

deleted_user — Fri, 04 Jun 2010 08:42:43 GMT

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

Re: Problems with query design

deleted_user — Fri, 04 Jun 2010 09:19:49 GMT

it works ... thank you.

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