topic processing date scenario in SAS Procedures

processing date scenario

raveena — Wed, 14 Sep 2011 21:16:26 GMT

Hi ,

I have a clamno, pidate ,lineno in a table.Each clamno can have multiple lineno in the table.Below is an example,

Processing month : september

Clamno lineno pidate

12345 001 09/27/2011

12345 002 08/16/2011

12345 003 10/11/2011

In this above scenario , i should not select this claim because the pidate is greater than the processing month.

23456 001 09/27/2011

23456 002 08/15/2011

23456 003 07/14/2011

In this above scenario, i should select this whole claim because the pidate is within the processing month.

13456 001 07/27/2011

13456 002 08/15/2011

13456 003 07/14/2011

In this above scenario, i should not select this whole claim because the pidate is not within the processing month.

so my output should be :

23456 001 09/27/2011

23456 002 08/15/2011

23456 003 07/14/2011

Please let me know how to process this dates.

processing date scenario

art297 — Wed, 14 Sep 2011 21:29:40 GMT

Not sure I really understand your requirements, but I think it is that the maximum pidate must be in the month you are looking for. If so, the following would work:

proc sql noprint;

create table want as

select *

from have

group by clamno

having max(month(pidate)) eq 9

;

quit;

Re: processing date scenario

FriedEgg — Wed, 14 Sep 2011 21:30:14 GMT

data have;

format pidate mmddyy10.;

input (clamno lineno) ($) pidate mmddyy10.;

cards

;

12345 001 09/27/2011

12345 002 08/16/2011

12345 003 10/11/2011

23456 001 09/27/2011

23456 002 08/15/2011

23456 003 07/14/2011

13456 001 07/27/2011

13456 002 08/15/2011

13456 003 07/14/2011

;

run;

proc sql;

select *

from have

group by clamno

having month(max(pidate))=9;

quit;

EDIT: ART beat me

Re: processing date scenario

art297 — Wed, 14 Sep 2011 22:03:23 GMT

FriedEgg,

Don't feel too bad about it, you beat me on raveena's last question. Besides, this poster apparently doesn't realize that the poster should indicate an answer as correct anyhow and I was only seconds ahead of you.

Re: processing date scenario

raveena — Thu, 15 Sep 2011 19:37:30 GMT

Hi,

I need to clarify one more thing with you.

Need to create a new field called version number.

- If the whole clamno that has the ind='Y' then the version number should be '1'.

- When the field pidate month in the clamno is with in the processing month then the version number is"0".

- when the field pidate is earlier than the processing month, then the version number is "1"

Processing month : september

data have;

format pidate mmddyy10.;

input (clamno lineno) ($) pidate revind mmddyy10.;

cards

;

12345 001 09/27/2011 Y

12345 002 08/16/2011 N

12345 003 10/11/2011 Y

23456 001 09/27/2011 Y

23456 002 08/15/2011 Y

23456 003 07/14/2011 Y

12390 001 09/02/2011 Y

12390 002 08/02/2011 N

13456 001 07/27/2011 N

13456 002 08/15/2011 N

13456 003 07/14/2011 Y

;

run;

Output should be:

clamno lnno pidate ind versionnumber

23456 001 09/27/2011 Y 1

23456 002 08/15/2011 Y 1

23456 003 07/14/2011 Y 1

12390 001 09/02/2011 Y 0

12390 002 08/02/2011 N 1

Please help me out in this above logic.

Thanks in Advance.

processing date scenario

FriedEgg — Thu, 15 Sep 2011 20:26:31 GMT

Hmm, I deleted my previous comment because it was dumb, but apparently it removed Art and my answers as well, seems like a issue with the forum???

data have;

format pidate mmddyy10.;

input (clamno lineno) ($) pidate mmddyy10. revind $;

cards

;

12345 001 09/27/2011 Y

12345 002 08/16/2011 N

12345 003 10/11/2011 Y

23456 001 09/27/2011 Y

23456 002 08/15/2011 Y

23456 003 07/14/2011 Y

12390 001 09/02/2011 Y

12390 002 08/02/2011 N

92390 001 09/02/2011 Y

92390 002 08/02/2011 Y

13456 001 07/27/2011 N

13456 002 08/15/2011 N

13456 003 07/14/2011 Y

;

run;

proc sql;

select clamno,lineno,pidate,revind,

case

when month(pidate)=9 then 0

when min(revind)='Y' and max(revind)='Y' then 1

else 1

end as versionnumber

from have

group by clamno

having month(max(pidate))=9

order by clamno,lineno;

quit;

processing date scenario

art297 — Thu, 15 Sep 2011 20:29:17 GMT

Yes, when one deletes a post it deletes all of the posts that responded to it. When I saw that it was gone I was going to repost it anyway, but thank you for doing so.

processing date scenario

FriedEgg — Thu, 15 Sep 2011 20:38:55 GMT

What should happen when you have all in a group with 'Y' and the max(date)=process_month? Should it be 1 or 0? In the logic previously posted the result will be 0.

92390 001 09/02/2011 Y 0

92390 002 08/02/2011 Y 1

processing date scenario

art297 — Thu, 15 Sep 2011 20:42:02 GMT

Raveena spelled that out in the requirements:

- If the whole clamno that has the ind='Y' then the version number should be '1'.

The code I proposed, I think, handles all of the requirements.

processing date scenario

FriedEgg — Thu, 15 Sep 2011 20:58:34 GMT

His requirements said;

if the whole clamno that has the ind='Y' then the version number should be '1'

- When the field pidate month in the clamno is with in the processing month then the version number is"0".

So this instance meets both criteria and wondering which takes precedence?

processing date scenario

art297 — Thu, 15 Sep 2011 21:32:47 GMT

Since the example provided was the specific case you are asking about I think it was safe to presume that if the first condition was met, the others weren't relevant. However, of course, they're not MY requirements.

processing date scenario

raveena — Fri, 16 Sep 2011 20:19:45 GMT

Hi Matthew,

I have misspelled the requirement ,

-When the clamno has the revind='Y' then the version number should be 1.

- When the field pidate month in the clamno is with in the processing month then the version number is"0".

- when the field pidate is earlier than the processing month, then the version number is "1"

However, Art297 logic code is working fine for my requirements.

Thanks ..

processing date scenario

Ksharp — Sat, 17 Sep 2011 02:12:42 GMT

data have;
format pidate mmddyy10.;
input (clamno lineno) ($) pidate mmddyy10. revind $;
cards;
12345 001 09/27/2011 Y
12345 002 08/16/2011 N
12345 003 10/11/2011 Y
23456 001 09/27/2011 Y
23456 002 08/15/2011 Y
23456 003 07/14/2011 Y
12390  001 09/02/2011 Y
12390  002 08/02/2011 N
13456 001 07/27/2011 N
13456 002 08/15/2011 N
13456 003 07/14/2011 Y
;
run;
proc sql noprint;
 create table want as
  select *,1 as flag
   from have
    group by clamno
      having count(distinct revind) eq 1 and month(max(pidate)) eq 9
 union
  select *,case when month(pidate)=9 then 0 else 1 end as flag
   from have
    group by clamno
      having count(distinct revind) eq 2 and month(max(pidate)) eq 9;
quit;
proc sort data=want;by clamno;run;

Ksharp

processing date scenario

raveena — Mon, 19 Sep 2011 16:06:46 GMT

Thanks KSharp !!!