BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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.

7 REPLIES 7
shivas
Pyrite | Level 9

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
Quartz | Level 8

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
Opal | Level 21

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
Quartz | Level 8

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

art297
Opal | Level 21

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
SAS Employee

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
Opal | Level 21

FriedEgg: I think that should be

group by id, date, code

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1508 views
  • 1 like
  • 4 in conversation