Help using Base SAS procedures

SAS QUERY

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

SAS QUERY

I have dataset with ID AND VISIT variables....A ID can have indefinite number of visits staring from 1 to n.....I would like to write query in such a way for every ID there there should be VISIT=2.......If visit=2 is missing for any ID....then we should report that ID as discrepancy....The below highlighted is a discrepancy and should output it....

100010001    0

100010001    1

100010001    2

100010001    3

100010001    4

100010001    5

100010002    0

100010002    1

100010002    2

100010002    3

100010002    4

100010002    5

100010002    6

100010002    7

100010003    0

100010003    1

100010003    3

100010003    4

100010003    5

100010003    6


Accepted Solutions
Solution
‎05-21-2015 02:08 PM
Valued Guide
Posts: 858

Re: SAS QUERY

Here you go:

data have;

length id $9.;

input ID $ visit;

cards;

100010001    0

100010001    1

100010001    2

100010001    3

100010001    4

100010001    5

100010002    0

100010002    1

100010002    2

100010002    3

100010002    4

100010002    5

100010002    6

100010002    7

100010003    0

100010003    1

100010003    3

100010003    4

100010003    5

100010003    6

run;

data good bad;

do until (last.id);

set have;

by id;

if visit = 2 then flag = 1;

end;

do until (last.id);

set have;

by id;

if flag = 1 then output good;

if flag ne 1 then output  bad;

end;

drop flag;

run;

View solution in original post


All Replies
Super User
Posts: 10,538

Re: SAS QUERY

What should the OUTPUT look like? A report or a dataset?

data _null_;

     set have;

     by id;

  if not first.Id and (dif(visit) ne 1) then put Id "discrepancy at visit " visit;

run;

might give some hints.

Solution
‎05-21-2015 02:08 PM
Valued Guide
Posts: 858

Re: SAS QUERY

Here you go:

data have;

length id $9.;

input ID $ visit;

cards;

100010001    0

100010001    1

100010001    2

100010001    3

100010001    4

100010001    5

100010002    0

100010002    1

100010002    2

100010002    3

100010002    4

100010002    5

100010002    6

100010002    7

100010003    0

100010003    1

100010003    3

100010003    4

100010003    5

100010003    6

run;

data good bad;

do until (last.id);

set have;

by id;

if visit = 2 then flag = 1;

end;

do until (last.id);

set have;

by id;

if flag = 1 then output good;

if flag ne 1 then output  bad;

end;

drop flag;

run;

Respected Advisor
Posts: 4,659

Re: SAS QUERY

You should close your before opening this one.

PG
Regular Contributor
Posts: 168

Re: SAS QUERY

Hi Rakesh,

If i understood right!!! this is one way to get the output you want...

Below code is DoW approach!!!

data want;

do until(last.id);

  set have;

   by id;

   if visit eq 2 then flg=1;

end;

do until(last.id);

set have;

by id;

if missing(flg) then output;

end;

run;


Thanks

Sam

PROC Star
Posts: 1,099

Re: SAS QUERY

Not thoroughly tested:

proc sql;

create table want as

   select distinct ID from have

                         left outer join

                            (select ID as GoodID from have where Visit = 2)

                         on ID = GoodID

                      where GoodID is null;

quit;

Tom

Super User
Posts: 9,688

Re: SAS QUERY

proc sql;

create table want as

select * from have group by id having sum(visit=2)=0;

quit;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 319 views
  • 3 likes
  • 7 in conversation