BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Msilverio
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

View solution in original post

9 REPLIES 9
Msilverio
Calcite | Level 5

Kurt,

This is what I want the expected results to look like

SITESA_IDCA_IDREVN_MO_DESC1RSD_CU_CNTFIRSTLAST
9386802872502863528462882009-01110
9386802872502863528462882009-02100
9386802872502863528462882009-03100
9386802872502863528462882009-03001
9386802872502864969562872009-04011
9386802872502865047442822009-04011
9386802872502864969562872009-05110
9386802872502864969562872009-06100
9386802872502864969562872009-07100
9386802872502864969562872009-08100
9386802872502864969562872009-09100
9386802872502864969562872009-10100
9386802872502864969562872009-11100
9386802872502864969562872009-12100
9386802872502864969562872010-01000
9386802872502864969562872010-01100
9386802872502864969562872010-02100
9386802872502864969562872010-03100
9386802872502864969562872010-04100
9386802872502864969562872010-05100
9386802872502864969562872010-06100
9386802872502864969562872010-07100
9386802872502864969562872010-08100
9386802872502864969562872010-09100
9386802872502864969562872010-10001
9386802872502865047442822010-10011
9386802872502869024272892011-09010
9386802872502869024272892011-10100
9386802872502869024272892011-11100
9386802872502869024272892011-12101
9386802872502865047442822012-01011
9386802872502869024272892012-01010
9386802872502869024272892012-01100
9386802872502869024272892012-01001
9386802872502865047442822012-02011
9386802872502867613472882012-02011
9386802872502865047442822012-03011
9386802872502867613472882012-03011
9386802872502865047442822012-04110
9386802872502865047442822012-05100
9386802872502865047442822012-06100
9386802872502865047442822012-07100
9386802872502865047442822012-07000
9386802872502865047442822012-08100
9386802872502865047442822012-08001
9386802872502865368372812012-09011
9386802872502866976572882012-09011
9386802872502866976572882012-09000
93868028725028651472802012-10011
9386802872502865047442822012-10011
9386802872502865047442822012-10000
9386802872502866185672852012-10011
9386802872502865047442822012-11110
9386802872502865047442822012-12100
9386802872502865047442822013-01100
9386802872502865047442822013-02100
9386802872502865047442822013-02001
9386802872502869163652812013-02011
9386802872502865047442822013-03110
9386802872502865047442822013-03001
9386802872502867628772862013-03011
9386802872502865047442822013-04010
9386802872502865047442822013-04101
9386802872502862960872872013-05110
9386802872502862960872872013-06100
9386802872502862960872872013-07100
9386802872502862960872872013-08001
9386802872502862264972802013-09010
9386802872502862264972802013-10001
9386802872502865047442822013-10010
9386802872502865047442822013-11100
9386802872502865047442822013-12100
9386802872502865047442822014-01100
9386802872502865047442822014-02100
9386802872502865047442822014-03001
9386802872502868289082832014-03010
9386802872502868289082832014-04100
9386802872502868289082832014-05001
9386802872502865047442822014-09011
9386802872502869937472862014-09011
9386802872502865047442822014-10110
9386802872502865047442822014-11100
9386802872502865047442822014-12100
9386802872502865047442822015-01100
9386802872502865047442822015-02100
9386802872502865047442822015-03000
9386802872502865047442822015-03101
Vish33
Lapis Lazuli | Level 10

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
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Onyx | Level 15

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
Calcite | Level 5

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

shubhayog
Obsidian | Level 7

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
Calcite | Level 5

data want;

set have nobs=last;

if _n_ =1 or _n_=last ;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1264 views
  • 5 likes
  • 7 in conversation