topic Re: Help on creating a first and last observation for a report due tomorow in SAS Programming

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

Msilverio — Wed, 15 Apr 2015 06:21:30 GMT

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 5147280 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

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

Kurt_Bremser — Wed, 15 Apr 2015 06:38:32 GMT

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

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

Msilverio — Wed, 15 Apr 2015 06:46:21 GMT

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

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

Vish33 — Wed, 15 Apr 2015 07:25:43 GMT

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;

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

RW9 — Wed, 15 Apr 2015 08:35:17 GMT

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).

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

Msilverio — Wed, 15 Apr 2015 17:24:37 GMT

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

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

Haikuo — Wed, 15 Apr 2015 18:14:48 GMT

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;

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

shubhayog — Wed, 15 Apr 2015 18:33:01 GMT

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

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

Msilverio — Wed, 15 Apr 2015 18:33:30 GMT

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

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

Saidurga1 — Wed, 26 Jul 2017 10:00:58 GMT

data want;

set have nobs=last;

if _n_ =1 or _n_=last ;

run;