Modifier logic

[SASPhile]

id	date	code	modifier
12345	07/29/2011	76770
12345	07/29/2011	76770
12345	07/29/2011	76770	26
12345	07/29/2011	76770	26
12345	07/29/2011	76770	TC
12345	07/29/2011	77052
12345	07/29/2011	77052
12345	07/29/2011	77052	26
12345	02/26/2011
12345	02/26/2011	70450	26
12345	02/26/2011	70544	26
12345	02/26/2011	70549	26
12345	02/26/2011	70553	26
12345	02/26/2011	71010	26
34567	09/23/2011	74000	TC
34567	09/23/2011	74000
34567	09/23/2011	74000	26
34567	09/30/2011	70486
34567	09/30/2011	70486
34567	09/30/2011	70486	26
34567	02/22/2011	71020
56789	04/05/2011	74176
56789	04/05/2011	74176
56789	04/05/2011	74176	26

on the same date if an id has same code and if modifier is not missing then do not select those rows.for instance for id 12345 on 07/29/11 id has two different codes 76770 and 77052, out of 8 rows select only 4 rows where modifier is missing.

[shivas]

Hi,

Is this what you want..or i have not understood your problem.Can you paste the output you want.

data one;

input id date code modifier $ 29-31;

informat date mmddyy10.;

format date date9.;

cards;

12345 07/29/2011 76770

12345 07/29/2011 76770

12345 07/29/2011 76770 26

12345 07/29/2011 76770 26

12345 07/29/2011 76770 TC

12345 07/29/2011 77052

12345 07/29/2011 77052

12345 07/29/2011 77052 26

12345 02/26/2011

12345 02/26/2011 70450 26

12345 02/26/2011 70544 26

12345 02/26/2011 70549 26

12345 02/26/2011 70553 26

12345 02/26/2011 71010 26

34567 09/23/2011 74000 TC

34567 09/23/2011 74000

34567 09/23/2011 74000 26

34567 09/30/2011 70486

34567 09/30/2011 70486

34567 09/30/2011 70486 26

34567 02/22/2011 71020

56789 04/05/2011 74176

56789 04/05/2011 74176

56789 04/05/2011 74176 26

;

run;

proc sql;

create table want as

select * from one where modifier = ''

group by id, date;

quit;

Thanks,

Shiva

[SASPhile]

I'm sorry.The conditions are:

on the same date if an id has same code and if modifier is not missing then do not select those rows.

for instance for id 12345 on 07/29/11 id has two different codes 76770 and 77052, out of 8 rows select only 4 rows where modifier is missing.

if there is only one code and whatever be the modifier we will select that row.

for instance for id 12345 on 02/26/11 we need to select all the rows.

for 34567 0n 09/23/11 we select 74000 where modifier is missing.

[art297]

If I understand correctly, and your data are already sorted (as shown in your example), then how about something like:

data want;

  set have;

  by id date code notsorted;

  if (first.code and last.code) or

    missing(modifier);

run;

[SASPhile]

even if they are sorted, notsorted can be used right?

[art297]

Yes but, if they aren't in the correct order for your logic, then you would have to sort them and then the notsorted option wouldn't have to be there.  However, it wouldn't hurt.

[FriedEgg]

proc sql;

select *

   from one

  group by id, date, code

having missing(modifier) or count(*)=1;

quit;

Art, you are correct, however with the example data results will be the same.

Message was edited by: Matthew Kastin, edit is in bold

[art297]

FriedEgg: I think that should be

group by id, date, code