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.
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
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;
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
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.
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.
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;
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.
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
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.
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?
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.
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 ..
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
Thanks KSharp !!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.