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
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;
Could you post a "want" dataset to clarify your task?
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 |
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;
unfortunatelly this is not producing similar reports to the end result of the "WANT" data set i posted above? Any ideas?
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).
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;
Thanks Hai.Kou this worked flawlessly, you saved the day. I'm assuming the similar process will work on a large data set.
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
data want;
set have nobs=last;
if _n_ =1 or _n_=last ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.