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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee

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

13 REPLIES 13
art297
Opal | Level 21

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;

FriedEgg
SAS Employee

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

art297
Opal | Level 21

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.

raveena
Obsidian | Level 7

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.

FriedEgg
SAS Employee

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;

art297
Opal | Level 21

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.

FriedEgg
SAS Employee

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

art297
Opal | Level 21

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.

FriedEgg
SAS Employee

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?

art297
Opal | Level 21

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.

raveena
Obsidian | Level 7

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

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

raveena
Obsidian | Level 7

Thanks 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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 13 replies
  • 1062 views
  • 6 likes
  • 4 in conversation