Help on creating a first and last observation for a report due tomorow

[Msilverio]

Hello,

I'm in the process of  trying to correctly label first and last observations by a specific sorting criteria (Service account, Month and Customer Account ). In this process I need to correctly assignt first and last observations to customer accounts to identify turn ove by  moth.. Can anyone point me in the right direction to fix this issue?

I can't seem to get this to work properly without having to resort the data. The sorting criteria must stay as;

1. SA_ID

2. Month    (REVN_MO_DESC1)

3. Customer Account  (CA_ID)

Thoughs on how to get this work?

DATA:

SA_ID CA_ID REVN_MO_DESC1 RSD_CU_CNT

250286 352846288 2009-01 1

250286 352846288 2009-02 1

250286 352846288 2009-03 1

250286 352846288 2009-03 0

250286 496956287 2009-04 0

250286 504744282 2009-04 0

250286 496956287 2009-05 1

250286 496956287 2009-06 1

250286 496956287 2009-07 1

250286 496956287 2009-08 1

250286 496956287 2009-09 1

250286 496956287 2009-10 1

250286 496956287 2009-11 1

250286 496956287 2009-12 1

250286 496956287 2010-01 0

250286 496956287 2010-01 1

250286 496956287 2010-02 1

250286 496956287 2010-03 1

250286 496956287 2010-04 1

250286 496956287 2010-05 1

250286 496956287 2010-06 1

250286 496956287 2010-07 1

250286 496956287 2010-08 1

250286 496956287 2010-09 1

250286 496956287 2010-10 0

250286 504744282 2010-10 0

250286 902427289 2011-09 0

250286 902427289 2011-10 1

250286 902427289 2011-11 1

250286 902427289 2011-12 1

250286 504744282 2012-01 0

250286 902427289 2012-01 0

250286 902427289 2012-01 1

250286 902427289 2012-01 0

250286 504744282 2012-02 0

250286 761347288 2012-02 0

250286 504744282 2012-03 0

250286 761347288 2012-03 0

250286 504744282 2012-04 1

250286 504744282 2012-05 1

250286 504744282 2012-06 1

250286 504744282 2012-07 1

250286 504744282 2012-07 0

250286 504744282 2012-08 1

250286 504744282 2012-08 0

250286 536837281 2012-09 0

250286 697657288 2012-09 0

250286 697657288 2012-09 0

250286 5147280 2012-10 0

250286 504744282 2012-10 0

250286 504744282 2012-10 0

250286 618567285 2012-10 0

250286 504744282 2012-11 1

250286 504744282 2012-12 1

250286 504744282 2013-01 1

250286 504744282 2013-02 1

250286 504744282 2013-02 0

250286 916365281 2013-02 0

250286 504744282 2013-03 1

250286 504744282 2013-03 0

250286 762877286 2013-03 0

250286 504744282 2013-04 0

250286 504744282 2013-04 1

250286 296087287 2013-05 1

250286 296087287 2013-06 1

250286 296087287 2013-07 1

250286 296087287 2013-08 0

250286 226497280 2013-09 0

250286 226497280 2013-10 0

250286 504744282 2013-10 0

250286 504744282 2013-11 1

250286 504744282 2013-12 1

250286 504744282 2014-01 1

250286 504744282 2014-02 1

250286 504744282 2014-03 0

250286 828908283 2014-03 0

250286 828908283 2014-04 1

250286 828908283 2014-05 0

250286 504744282 2014-09 0

250286 993747286 2014-09 0

250286 504744282 2014-10 1

250286 504744282 2014-11 1

250286 504744282 2014-12 1

250286 504744282 2015-01 1

250286 504744282 2015-02 1

250286 504744282 2015-03 0

250286 504744282 2015-03 1

[Kurt_Bremser]

Could you post a "want" dataset to clarify your task?

[Msilverio]

Kurt,

This is what I want the expected results to look like

SITE	SA_ID	CA_ID	REVN_MO_DESC1	RSD_CU_CNT	FIRST	LAST
938680287	250286	352846288	2009-01	1	1	0
938680287	250286	352846288	2009-02	1	0	0
938680287	250286	352846288	2009-03	1	0	0
938680287	250286	352846288	2009-03	0	0	1
938680287	250286	496956287	2009-04	0	1	1
938680287	250286	504744282	2009-04	0	1	1
938680287	250286	496956287	2009-05	1	1	0
938680287	250286	496956287	2009-06	1	0	0
938680287	250286	496956287	2009-07	1	0	0
938680287	250286	496956287	2009-08	1	0	0
938680287	250286	496956287	2009-09	1	0	0
938680287	250286	496956287	2009-10	1	0	0
938680287	250286	496956287	2009-11	1	0	0
938680287	250286	496956287	2009-12	1	0	0
938680287	250286	496956287	2010-01	0	0	0
938680287	250286	496956287	2010-01	1	0	0
938680287	250286	496956287	2010-02	1	0	0
938680287	250286	496956287	2010-03	1	0	0
938680287	250286	496956287	2010-04	1	0	0
938680287	250286	496956287	2010-05	1	0	0
938680287	250286	496956287	2010-06	1	0	0
938680287	250286	496956287	2010-07	1	0	0
938680287	250286	496956287	2010-08	1	0	0
938680287	250286	496956287	2010-09	1	0	0
938680287	250286	496956287	2010-10	0	0	1
938680287	250286	504744282	2010-10	0	1	1
938680287	250286	902427289	2011-09	0	1	0
938680287	250286	902427289	2011-10	1	0	0
938680287	250286	902427289	2011-11	1	0	0
938680287	250286	902427289	2011-12	1	0	1
938680287	250286	504744282	2012-01	0	1	1
938680287	250286	902427289	2012-01	0	1	0
938680287	250286	902427289	2012-01	1	0	0
938680287	250286	902427289	2012-01	0	0	1
938680287	250286	504744282	2012-02	0	1	1
938680287	250286	761347288	2012-02	0	1	1
938680287	250286	504744282	2012-03	0	1	1
938680287	250286	761347288	2012-03	0	1	1
938680287	250286	504744282	2012-04	1	1	0
938680287	250286	504744282	2012-05	1	0	0
938680287	250286	504744282	2012-06	1	0	0
938680287	250286	504744282	2012-07	1	0	0
938680287	250286	504744282	2012-07	0	0	0
938680287	250286	504744282	2012-08	1	0	0
938680287	250286	504744282	2012-08	0	0	1
938680287	250286	536837281	2012-09	0	1	1
938680287	250286	697657288	2012-09	0	1	1
938680287	250286	697657288	2012-09	0	0	0
938680287	250286	5147280	2012-10	0	1	1
938680287	250286	504744282	2012-10	0	1	1
938680287	250286	504744282	2012-10	0	0	0
938680287	250286	618567285	2012-10	0	1	1
938680287	250286	504744282	2012-11	1	1	0
938680287	250286	504744282	2012-12	1	0	0
938680287	250286	504744282	2013-01	1	0	0
938680287	250286	504744282	2013-02	1	0	0
938680287	250286	504744282	2013-02	0	0	1
938680287	250286	916365281	2013-02	0	1	1
938680287	250286	504744282	2013-03	1	1	0
938680287	250286	504744282	2013-03	0	0	1
938680287	250286	762877286	2013-03	0	1	1
938680287	250286	504744282	2013-04	0	1	0
938680287	250286	504744282	2013-04	1	0	1
938680287	250286	296087287	2013-05	1	1	0
938680287	250286	296087287	2013-06	1	0	0
938680287	250286	296087287	2013-07	1	0	0
938680287	250286	296087287	2013-08	0	0	1
938680287	250286	226497280	2013-09	0	1	0
938680287	250286	226497280	2013-10	0	0	1
938680287	250286	504744282	2013-10	0	1	0
938680287	250286	504744282	2013-11	1	0	0
938680287	250286	504744282	2013-12	1	0	0
938680287	250286	504744282	2014-01	1	0	0
938680287	250286	504744282	2014-02	1	0	0
938680287	250286	504744282	2014-03	0	0	1
938680287	250286	828908283	2014-03	0	1	0
938680287	250286	828908283	2014-04	1	0	0
938680287	250286	828908283	2014-05	0	0	1
938680287	250286	504744282	2014-09	0	1	1
938680287	250286	993747286	2014-09	0	1	1
938680287	250286	504744282	2014-10	1	1	0
938680287	250286	504744282	2014-11	1	0	0
938680287	250286	504744282	2014-12	1	0	0
938680287	250286	504744282	2015-01	1	0	0
938680287	250286	504744282	2015-02	1	0	0
938680287	250286	504744282	2015-03	0	0	0
938680287	250286	504744282	2015-03	1	0	1

[Vish33]

Not able to see any Site variable here. Any ways try the below code for rest.

proc sort data=have ;
by SA_ID CA_ID REVN_MO_DESC1 ;
run;

data want;
set have;
by SA_ID CA_ID REVN_MO_DESC1  ;
if first.REVN_MO_DESC1 then do;
     First=1 ;  Last=0 ;end;
  if last.REVN_MO_DESC1 then do;
         First=0 ;  Last=1 ; end;
run;

[Msilverio]

unfortunatelly this is not producing similar reports to the end result of the "WANT" data set i posted above? Any ideas?

[RW9]

So the data has to remain exactly sorted the way it is?  Well, two options as I see:

- Create an order variable in the dataset, i.e. ord=_n_;  This will then keep the sequence as it was.  Next, sort the data and assign first/last variable as you normally would.  Then just sort the dataset again by the ord variable and drop ord.  Presto, dataset is as it was before with the additional columns.

- Use SQL, if you don't specify the order or group by then it should not change the order in the data:

proc sql;

     create table WANT as

     select     A.*,

                   case when B.SITE is not null then 1

                           else 0 end as FIRST,

                   case when C.SITE is not null then 1

                           else 0 end as LAST

     from        HAVE A

     left join    (select SITE,SA_ID,CAID,REVN_MO_DESC1

                    from HAVE

                    group by SITE,SA_ID,CAID

                    having input(strip(REVN_MO_DESC1)||"-01",yymmdd10.)=min(input(strip(REVN_MO_DESC1)||"-01",yymmdd10.)) B

     on           A.SITE=B.SITE

     and          A.SA_ID=B.SA_ID

     and          A.CAID=B.CAID

     and          A.REVN_MO_DESC1=B.REVN_MO_DESC1

     left join  (select SITE,SA_ID,CAID,REVN_MO_DESC1

                    from HAVE

                    group by SITE,SA_ID,CAID

                    having input(strip(REVN_MO_DESC1)||"-01",yymmdd10.)=max(input(strip(REVN_MO_DESC1)||"-01",yymmdd10.)) C

     on           A.SITE=C.SITE

     and          A.SA_ID=C.SA_ID

     and          A.CAID=C.CAID

     and          A.REVN_MO_DESC1=C.REVN_MO_DESC1;

quit;

A bit messy, but something like that should work (haven't tested).

[Haikuo · Accepted solution]

You will need to trick SAS on this one:

proc sort data=have;

by SA_ID  REVN_MO_DESC1 CA_ID;

run;

data want;

set have;

by CA_ID notsorted;

first=first.ca_id;

last=last.ca_id;

run;

[Msilverio]

Thanks Hai.Kou this worked flawlessly, you saved the day. I'm assuming the similar process will work on a large data set.

[shubhayog]

Hi,

Please try following code, Hope It will work as you expecting.

data have1;

  set have;

  drop REVN_MO_DESC1 RSD_CU_CNT;

run;

proc sort

  data=have1

  out=have2 nodup;

  by _all_;

run;

data have3;

  set have2;

  n+1;

run;

proc sort

  data=have

  out=have4;

  by SITE SA_ID CA_ID REVN_MO_DESC1;

run;

data have5;

  merge have4 have3;

  by SITE SA_ID CA_ID;

run;

data want;

  set have5;

  if first.n then FIRST=1; else FIRST=0;

  if last.n then LAST=1; else LAST=0;

  by n;

  drop n;

run;

Regards,

Yogesh

[Saidurga1]

data want;

set have nobs=last;

if _n_ =1 or _n_=last ;

run;