topic switching values based on id and date in SAS Programming

switching values based on id and date

sasg — Wed, 29 Jun 2011 23:37:25 GMT

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,

switching values based on id and date

RD2 — Thu, 30 Jun 2011 09:04:19 GMT

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

switching values based on id and date

manojinpec — Thu, 30 Jun 2011 09:42:09 GMT

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;

switching values based on id and date

sasg — Thu, 30 Jun 2011 19:11:45 GMT

Thanks everybody.....it worked.

Randy Dai

I will answer u r question soon

switching values based on id and date

Ksharp — Fri, 01 Jul 2011 02:29:23 GMT

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

switching values based on id and date

sasg — Sun, 07 Aug 2011 02:32:33 GMT

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.

switching values based on id and date

art297 — Sun, 07 Aug 2011 04:27:56 GMT

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;

switching values based on id and date

art297 — Mon, 08 Aug 2011 03:55:04 GMT

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;

switching values based on id and date

sasg — Mon, 08 Aug 2011 21:53:53 GMT

Hi art297,

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

Thanks,

Rk.

Re: switching values based on id and date

MikeZdeb — Mon, 08 Aug 2011 22:06:06 GMT

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.

switching values based on id and date

sasg — Wed, 10 Aug 2011 18:03:42 GMT

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.

switching values based on id and date

art297 — Wed, 10 Aug 2011 18:25:33 GMT

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;

switching values based on id and date

Ksharp — Thu, 11 Aug 2011 07:25:24 GMT

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