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

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

1 ACCEPTED SOLUTION

Accepted Solutions
manojinpec
Obsidian | Level 7

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;

View solution in original post

12 REPLIES 12
RD2
Fluorite | Level 6 RD2
Fluorite | Level 6

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
Obsidian | Level 7

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

Thanks everybody.....it worked.

Randy Dai

I will answer u r question soon

Ksharp
Super User

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

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
Opal | Level 21

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
Opal | Level 21

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

Hi art297,

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

Thanks,

Rk.

sasg
Calcite | Level 5

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
Rhodochrosite | Level 12

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
Opal | Level 21

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
Super User

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

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
  • 12 replies
  • 1216 views
  • 0 likes
  • 6 in conversation