Query Help

Reply
Contributor
Posts: 71

Query Help

{Table}

custcode shopno date

12         A1   01-jan-2010

12         A1   01-Jan-2010

13         B1   02-Jan-2010

12         A1   08-jan-2010

14        C1   10-Feb-2010

13        B1   13-Feb-2010

13        B1    13-Feb-2010

12       B1   15-Mar-2010

13       A1   10-apr-2010

output I would like as

{Table}

custcode first_shop  first_date   Last_shop  last_date

12         A1        01-Jan-2010      B1         15-Mar-2010            

13         B1        02-Jan-2010     A1         10-Apr-2010

14         C1        10-Feb-2010  

I tired with self join but it didn't helped me. Can you help in getting the Query please.

SAS Version:9.2(base sas)

regards

Pallis

Super User
Posts: 5,256

Re: Query Help

Seeing your code would help.

Left join should work in conjunction with min and max respectively.

another way to solve this is using BY and first. and last. logic in the data step.

Data never sleeps
Super Contributor
Posts: 644

Re: Query Help

Proc Sort data = have ;

  By custcode date ;

Run ;

Data limits ;

  Set have ;

  By custcode date ;

  Test = first.custcode + 2 * last.custcode ;

  If Test > 0 then output ;

Run ;

Data want ;

  Merge limits (where = (test in (1, 3))

                Rename = (shopno = first_shop

                          Date = first_date)

               )

             

        limits (where = (test = 2)

                Rename = (shopno = last_shop

                          Date = last_date)

               )

        ;

  By custcode ;

  Drop test ;

Run ;

Richard

Contributor
Posts: 71

Re: Query Help

Thanks for the reply. When I try the left join I'm getting duplicate records.

Super User
Posts: 5,256

Re: Query Help

Did you forget the GROUP BY?

Data never sleeps
Super Contributor
Posts: 307

Re: Query Help

RichardinOz's solution worked on my machine using the sample data you provided.

You could also try PROC SQL as per the following example (not elegant, but works):

data have ;
input custcode $ shopno $2. date :date11.;
format date date9.;
datalines;
12 A1 01-jan-2010
12 A1 01-Jan-2010
13 B1 02-Jan-2010
12 A1 08-jan-2010
14 C1 10-Feb-2010
13 B1 13-Feb-2010
13 B1 13-Feb-2010
12 B1 15-Mar-2010
13 A1 10-apr-2010
;
;;;;

proc sql;
create table want as
select q1.*
  , ( CASE
   WHEN q2.last_date = q1.first_date and q2.last_shop = q1.first_shop then ''
   ELSE q2.last_shop
   END ) as last_shop
  , ( CASE
   WHEN q2.last_date = q1.first_date and q2.last_shop = q1.first_shop then .
   ELSE q2.last_date
   END ) as last_date format=date9.
from
( select distinct t.custcode , t.shopno as first_shop , t.date as first_date
from have t
where t.date =
  ( select min ( t1.date )
  from have t1
  where t1.custcode = t.custcode ) ) q1
, ( select distinct t2.custcode , t2.shopno as last_shop , t2.date as last_date
from have t2
where t2.date =
  ( select max ( t3.date )
  from have t3
  where t2.custcode = t3.custcode ) ) q2
where q1.custcode = q2.custcode
;
quit;

Super Contributor
Posts: 644

Re: Query Help

@Fugue

Thanks for the affirmation.  I started to go down the SQL track but I felt there ought to be an easier way, using first.x and last.x.  The expression  first.custcode + 2 * last.custcode  has the value 1 for the first of several custcode records (last.custcode = 0), 2 for the last of several custcode records (first.custcode = 0), 3 for a single custcode record, and 0 for all other records.

@pallis

You now have several solutions.  You should flag one as the answer, and any others you found helpful.

Richard

Respected Advisor
Posts: 4,644

Re: Query Help

This way works :

proc sql;

create table want as

select *

from

(

     select custcode, shopno as firstShop, date as firstDate from have group by custcode

          having date=min(date)

) natural left join

(

     select custcode, shopno as lastShop, date as lastDate from have group by custcode

          having date > min(date) and date = max(date)

);

quit;

as long as no more than one shopno occurs on a given date and custcode.It is not clear what you would want as a result in such a case.

PG

PG
Ask a Question
Discussion stats
  • 7 replies
  • 323 views
  • 3 likes
  • 5 in conversation