DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

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


Accepted Solutions
Solution
‎04-15-2015 02:14 PM
Respected Advisor
Posts: 3,124

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

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


All Replies
Super User
Posts: 6,936

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

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

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
Frequent Contributor
Posts: 117

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

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;

Occasional Contributor
Posts: 6

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

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

Super User
Super User
Posts: 7,401

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

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


Solution
‎04-15-2015 02:14 PM
Respected Advisor
Posts: 3,124

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

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;

Occasional Contributor
Posts: 6

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

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

Occasional Contributor
Posts: 17

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

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

New Contributor
Posts: 3

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

data want;

set have nobs=last;

if _n_ =1 or _n_=last ;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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