switching values based on id and date

[sasg]

id and dates are same for first three types...if it is not in proper order then we have to arrange
type in this process referral,approval,ship.

proper order...referral,approval,ship

here is sample data:

id     type       date
1      approval   1/10/10
1      referral   1/10/10
1      ship       1/10/10
1      approval   1/10/10
1      ship       1/10/10
2      ship       2/10/10
2      referral   2/10/10
2      approval   2/10/10
2      ship       1/10/10
2      referral   1/10/10
2      approval    1/10/10

output should be like this:

1      referral   1/10/10
1      approval   1/10/10
1      ship       1/10/10
1      approval   1/10/10
1      ship       1/10/10
2      referral   2/10/10
2      approval   2/10/10
2      ship       2/10/10
2      referral   1/10/10
2      approval   1/10/10
2      ship       1/10/10

Can someone suggest me how to do this?

Thanks,

rk

[RD2]

Hi rk,

Your expected output still confuse  :

1      referral   1/10/10
1      approval   1/10/10
1      ship       1/10/10         <== Will computer treat these 2 as wrong order, ship before approval?
1      approval   1/10/10       <==  Will computer treat these 2 as wrong order,  ship before approval?
1      ship       1/10/10

Your raw data structure is not well or orgnized.

Best regards,

Randy Dai

[manojinpec · Accepted solution]

may be this would help

data dummy;
input id type $ in_date date9. ;
cards;
1 approval 01OCT2010
1 referral 01OCT2010
1 ship 01OCT2010
1 approval 01OCT2010
1 ship 01OCT2010
2 ship 02OCT2010
2 referral 02OCT2010
2 approval 02OCT2010
2 ship 01OCT2010
2 referral 01OCT2010
2 approval 01OCT2010
;
run;

  data dummy;
set dummy;
if type = 'approval' then temp_data=2;
if type='referral' then temp_data=1;
if type='ship' then temp_data=3;
run;

     proc sort data=dummy out=dummy_sort (drop=temp_data);
by id in_date temp_data ;
run;

[sasg]

Thanks everybody.....it worked.

Randy Dai

I will answer u r question soon

[Ksharp]

If your date is different  for each group of "referral,approval,ship". then manjoli 's code is correct.

But if date is the same with this group and next group for the same id?

data dummy;
input id type $ in_date date9. ;
format in_date date9.;
cards;
1 approval 01OCT2010
1 referral 01OCT2010
1 ship 01OCT2010
1 approval 01OCT2010
1 ship 01OCT2010
2 ship 02OCT2010
2 referral 02OCT2010
2 approval 02OCT2010
2 ship 01OCT2010
2 referral 01OCT2010
2 approval 01OCT2010
;
run;


data dummy;
set dummy;
if id ne lag(id) then do; a=0;r=0;s=0;end;
if type = 'approval' then do; temp_data=2; a+1;count=a;end;
else if type='referral' then do;temp_data=1; r+1;count=r;end;
 else if type='ship' then do;temp_data=3; ; s+1;count=s;end;
drop  a r s;
run;
proc sort data=dummy;
 by id count temp_data;
run;

Ksharp

[sasg]

Hi Ksharp,

          I'm using your code..i'm having issue with scenario in bold.here for id 1,records for dates 3/10/10 are not flipping(for ship and approval) .

          Can you pls help me on this scenario.

here is sample data:

id     type         date
1      approval   1/10/10
1      referral     1/10/10
1      ship         1/10/10

1      ship         2/10/10
1      ship         3/10/10
1     approval    3/10/10

2      ship       2/10/10
2      referral   2/10/10
2      approval   2/10/10
2      ship       1/10/10
2      referral   1/10/10
2      approval    1/10/10

output data:

here is sample data:

id     type         date
1      approval   1/10/10
1      referral     1/10/10
1      ship         1/10/10

1      ship         2/10/10
1     approval        3/10/10
1     ship    3/10/10

2      ship       2/10/10
2      referral   2/10/10
2      approval   2/10/10
2      ship       1/10/10
2      referral   1/10/10
2      approval    1/10/10.

Thanks,

rk.

[art297]

I can't test the following code tonight, but try it and see if it solves your problem:

data dummy;

input id type $ in_date date9. ;

format in_date date9.;

cards;

1 approval 01OCT2010

1 referral 01OCT2010

1 ship 01OCT2010

1 approval 01OCT2010

1 ship 01OCT2010

2 ship 02OCT2010

2 referral 02OCT2010

2 approval 02OCT2010

2 ship 01OCT2010

2 referral 01OCT2010

2 approval 01OCT2010

;

run;

data dummy;

  set dummy;

  if id ne lag(id) then do;

    count=0;

    counter=1;

  end

  if type='referral' then do;

    if count in (1,3,5,7) then do;

      count=0;

      counter+1;

    end;

    temp_data=1;

    count+1;

  end;

  else if type = 'approval' then do;

    if count in (2,3,6,7) then do;

      count=0;

      counter+1;

    end;

    temp_data=2;

    count+2;

  end;

  else if type='ship' then do;

    if count in (4,5,6,7) then do;

      count=0;

      counter+1;

    end;

    temp_data=3;

    count+4;

  end;

run;

proc sort data=dummy;

by id in_date counter temp_data;

run;

[art297]

I had left off a semi-colon and didn't get rid of the check fields.  See if the following does what you want:

data dummy;

input id type $ in_date date9. ;

format in_date date9.;

cards;

1 approval 01OCT2010

1 referral 01OCT2010

1 ship 01OCT2010

1 approval 01OCT2010

1 ship 01OCT2010

2 ship 02OCT2010

2 referral 02OCT2010

2 approval 02OCT2010

2 ship 01OCT2010

2 referral 01OCT2010

2 approval 01OCT2010

;

run;

data want (drop=count);

  set dummy;

  if id ne lag(id) then do;

    count=0;

    counter=1;

  end;

  if type='referral' then do;

    if count in (1,3,5,7) then do;

      count=0;

      counter+1;

    end;

    temp_data=1;

    count+1;

  end;

  else if type = 'approval' then do;

    if count in (2,3,6,7) then do;

      count=0;

      counter+1;

    end;

    temp_data=2;

    count+2;

  end;

  else if type='ship' then do;

    if count in (4,5,6,7) then do;

      count=0;

      counter+1;

    end;

    temp_data=3;

    count+4;

  end;

run;

proc sort data=want out=want (drop=counter temp_data);

  by id in_date counter temp_data;

run;

[sasg]

Hi art297,

           Your solution worked......Thanks a lot.

Thanks,

Rk.

[sasg]

Hi art297,

              I have another scenario below...in that scenario above logic is not working.

id  date            type

1702950901 03/22/2010 Referral
1702950901 03/29/2010 Approval
1702950901 03/29/2010 Referral
1702950901 03/30/2010 Ship
1702950901 05/19/2010 Ship
1702950901 08/02/2010 Ship
1702950901 08/31/2010 Ship
1702950901 09/20/2010 Ship
1702950901 10/18/2010 Ship
1702950901 11/15/2010 Ship
1702950901 12/13/2010 Ship
1702968901 05/18/2010 Referral
1702968901 06/04/2010 Approval
1702968901 06/22/2010 Referral
1702968901 06/29/2010 Ship
1702968901 08/23/2010 Ship
1702968901 09/28/2010 Ship
1702968901 10/20/2010 Ship

Could you please check that once.

Thanks,

rk.

[MikeZdeb]

Hi ... here's another idea.  Add an index to the data set.  Then, read the refererrals.  For each "referral", look first for an "approval" and then

a "ship" with the same ID and IN_DATE  ...

data x;

input id : $1. type : $8. in_date : date9. ;

format in_date date9.;

cards;

1 approval 01OCT2010

1 referral 01OCT2010

1 ship 01OCT2010

1 approval 01OCT2010

1 ship 01OCT2010

2 ship 02OCT2010

2 referral 02OCT2010

2 approval 02OCT2010

2 ship 01OCT2010

2 referral 01OCT2010

2 approval 01OCT2010

;

run;

proc datasets lib=work nolist;

modify x;

index create a=(id type in_date);

quit;

data xx;

set x (where=(type eq 'referral'));

output;

do until (_error_);

   type = 'approval';

   set x key=a;

   if ^ _error_ then output;

   type = 'ship';

   set x key=a;

   if ^ _error_ then output;

end;

_error_ = 0;

run;

proc print data=xx;

run;

id    type          in_date

1     referral    01OCT2010

1     approval    01OCT2010

1     ship        01OCT2010

1     approval    01OCT2010

1     ship        01OCT2010

2     referral    02OCT2010

2     approval    02OCT2010

2     ship        02OCT2010

2     referral    01OCT2010

2     approval    01OCT2010

2     ship        01OCT2010

ps  As pointed out by KSharp, the posting marked "correct" does not actually work with the data set in the original posting since it has multiple "approvals" and "ships" within an ID and with the same IN_DATE.

[art297]

rk,

You brought two new factors into the equation: your types now start with a capital letter and in_date is now relevant.  Try the following modifications to the original code:

data dummy;

  informat id $10.;

  informat in_date mmddyy10.;

input id in_date type $ ;

format in_date date9.;

cards;

1702950901 03/22/2010 Referral

1702950901 03/29/2010 Approval

1702950901 03/29/2010 Referral

1702950901 03/30/2010 Ship

1702950901 05/19/2010 Ship

1702950901 08/02/2010 Ship

1702950901 08/31/2010 Ship

1702950901 09/20/2010 Ship

1702950901 10/18/2010 Ship

1702950901 11/15/2010 Ship

1702950901 12/13/2010 Ship

1702968901 05/18/2010 Referral

1702968901 06/04/2010 Approval

1702968901 06/22/2010 Referral

1702968901 06/29/2010 Ship

1702968901 08/23/2010 Ship

1702968901 09/28/2010 Ship

1702968901 10/20/2010 Ship

;

run;

data want (drop=count);

  set dummy;

  if id ne lag(id) or in_date ne lag(in_date) then do;

    count=0;

    counter+1;

  end;

  if upcase(type)='REFERRAL' then do;

    if count in (1,3,5,7) then do;

      count=0;

      counter+1;

    end;

    temp_data=1;

    count+1;

  end;

  else if upcase(type) = 'APPROVAL' then do;

    if count in (2,3,6,7) then do;

      count=0;

      counter+1;

    end;

    temp_data=2;

    count+2;

  end;

  else if upcase(type)='SHIP' then do;

    if count in (4,5,6,7) then do;

      count=0;

      counter+1;

    end;

    temp_data=3;

    count+4;

  end;

run;

proc sort data=want out=want (drop=counter temp_data);

  by id in_date counter temp_data;

run;

[Ksharp]

Oh Dear. Your question is complicated!

data dummy;
input id type $ in_date : date9. ;
format in_date date9.;
cards;
1 approval 01OCT2010
1 referral 01OCT2010
1 ship 01OCT2010
1 approval 01OCT2010
1 ship 01OCT2010
1      ship        10feb2010
1      ship        10mar2010
1     approval    10mar2010
2 ship 02OCT2010
2 referral 02OCT2010
2 approval 02OCT2010
2 ship 01OCT2010
2 referral 01OCT2010
2 approval 01OCT2010
;
run;


data dummy;
set dummy;
if id ne lag(id) or in_date ne lag(in_date) then  _count+1; 
if id ne lag(id) then do; a=0;r=0;s=0;end;
if lowcase(type) = 'approval' then do; temp_data=2; a+1;count=a;end;
else if lowcase(type) ='referral' then do;temp_data=1; r+1;count=r;end;
 else if lowcase(type) ='ship' then do;temp_data=3; ; s+1;count=s;end;
drop  a r s;
run;
proc sort data=dummy;
 by id _count count temp_data;
run;

Ksharp