DATA Step, Macro, Functions and more

Modifier logic

Reply
Super Contributor
Posts: 647

Modifier logic

iddatecodemodifier
1234507/29/201176770
1234507/29/201176770
1234507/29/20117677026
1234507/29/20117677026
1234507/29/201176770TC
1234507/29/201177052
1234507/29/201177052
1234507/29/20117705226
1234502/26/2011
1234502/26/20117045026
1234502/26/20117054426
1234502/26/20117054926
1234502/26/20117055326
1234502/26/20117101026
3456709/23/201174000

          TC

3456709/23/201174000
3456709/23/20117400026
3456709/30/201170486
3456709/30/201170486
3456709/30/20117048626
3456702/22/201171020
5678904/05/201174176
5678904/05/201174176
5678904/05/20117417626

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.

Super Contributor
Posts: 349

Re: Modifier logic

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

Super Contributor
Posts: 647

Re: Modifier logic

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.


PROC Star
Posts: 7,356

Re: Modifier logic

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;

Super Contributor
Posts: 647

Re: Modifier logic

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

PROC Star
Posts: 7,356

Re: Modifier logic

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.

Trusted Advisor
Posts: 1,300

Re: Modifier logic

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

PROC Star
Posts: 7,356

Re: Modifier logic

FriedEgg: I think that should be

group by id, date, code

Ask a Question
Discussion stats
  • 7 replies
  • 701 views
  • 1 like
  • 4 in conversation