Help using Base SAS procedures

processing date scenario

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

processing date scenario

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.


Accepted Solutions
Solution
‎09-14-2011 05:30 PM
Trusted Advisor
Posts: 1,300

Re: processing date scenario

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 Smiley Sad

View solution in original post


All Replies
PROC Star
Posts: 7,363

processing date scenario

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;

Solution
‎09-14-2011 05:30 PM
Trusted Advisor
Posts: 1,300

Re: processing date scenario

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 Smiley Sad

PROC Star
Posts: 7,363

Re: processing date scenario

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.

Frequent Contributor
Posts: 90

Re: processing date scenario

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.

Trusted Advisor
Posts: 1,300

processing date scenario

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;

PROC Star
Posts: 7,363

processing date scenario

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.

Trusted Advisor
Posts: 1,300

processing date scenario

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

PROC Star
Posts: 7,363

processing date scenario

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.

Trusted Advisor
Posts: 1,300

processing date scenario

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?

PROC Star
Posts: 7,363

processing date scenario

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.

Frequent Contributor
Posts: 90

processing date scenario

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

Super User
Posts: 9,682

processing date scenario

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

Frequent Contributor
Posts: 90

processing date scenario

Thanks KSharp !!!

☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 178 views
  • 6 likes
  • 4 in conversation