DATA Step, Macro, Functions and more

Subject selection based on if conditional processing.

Reply
Contributor ved
Contributor
Posts: 27

Subject selection based on if conditional processing.

Hello SAS Folks,

i have a data set having multiple subj Id , where few subjects have multiple visits and few have only a single visit. Let say, the Day 0 is baseline visit and following visits are post baseline .

i need to select only subjects who have at least one post baseline visit and to eliminate subjects who have no post baseline . But here i have one subject who have post baseline visits but no data is recorded for those post baseline . i also need to eliminate these subjects who have post baseline visit but where no data is recorded.

However if any subject having at least one non missing post baseline data , we have to select its all observation regardless weather it is missing or not.

i have attached the data ( sample) for your reference.

Many thanks in advance.

regards

Ved

_USUBJID _VISNAME _PAGNAME VSBPSYS

01/001 DAY0 PAGE007   130

01/001 WEEK01 PAGE010

01/001 WEEK02 PAGE013

01/001 WEEK04 PAGE016

01/001 WEEK08 PAGE018

01/001 WEEK12 PAGE020

01/004 DAY0 PAGE007   130

01/004 WEEK01 PAGE010   130

01/004 WEEK02 PAGE013   149

01/004 WEEK04 PAGE016   144

02/003  DAY0 PAGE007   122

03/010 DAY0 PAGE007   129

03/010 WEEK01 PAGE010

03/010 WEEK02 PAGE013   135

so as per above , i need subjid 01/004 and 03/010.

Attachment
Contributor ved
Contributor
Posts: 27

Re: Subject selection based on if conditional processing.

I am using following code. its selecting few but not all as desired.

data vs_Rand1(keep=_usubjid _visname vsbpsys x _pagname);

  set vs_Rand;

  by _usubjid;

  if (first._usubjid and last._usubjid) or (not(first._usubjid or last._usubjid) and lag(vsbpsys) eq .)

  then x=1;

run;

Super User
Super User
Posts: 7,942

Re: Subject selection based on if conditional processing.

You can use exists() in SQL:

proc sql;

     create table WANT as

     select     A.*

     from        HAVE A

     where exists(select distinct USUBJID from HAVE where USUBJID=A.USUBJID and VISIT=0)

          and exists(select distinct USUBJID from HAVE where USUBJID=A.USUBJID and VISIT > 0 and SOMEDATA is not null);

quit;

Super User
Posts: 10,018

Re: Subject selection based on if conditional processing.

If I understood what you mean.

Code: Program

data have;
infile cards truncover;
input (_USUBJID _VISNAME _PAGNAME VSBPSYS) ($);
cards;
01/001 DAY0 PAGE007 130
01/001 WEEK01 PAGE010
01/001 WEEK02 PAGE013
01/001 WEEK04 PAGE016
01/001 WEEK08 PAGE018
01/001 WEEK12 PAGE020
01/004 DAY0 PAGE007 130
01/004 WEEK01 PAGE010 130
01/004 WEEK02 PAGE013 149
01/004 WEEK04 PAGE016 144
02/003 DAY0 PAGE007 122
03/010 DAY0 PAGE007 129
03/010 WEEK01 PAGE010
03/010 WEEK02 PAGE013 135
;
run;
data want;
do until(last._USUBJID );
  set have;
  by _USUBJID ;
  if _VISNAME =: 'WEEK' then do;
   has_week=1;
   if not missing(VSBPSYS) then has_value=1;
  end;
end;
do until(last._USUBJID );
  set have;
  by _USUBJID ;
  if has_week=1 and has_value=1 then output;
end;
drop has_week has_value;
run;
Contributor ved
Contributor
Posts: 27

Re: Subject selection based on if conditional processing.

thanks . it's working the way i wanted. can u please explain the coding logic.

Super User
Posts: 10,018

Re: Subject selection based on if conditional processing.

Sorry. I have no time to do that.

It is DOW skill. You can search it at this forum or at support.sas.com

You would get tons of paper or post to talk about it .

Xia Keshan

Contributor ved
Contributor
Posts: 27

Re: Subject selection based on if conditional processing.

xia keshan : Thanks for your valuable time. well in addition i would like to ask what if visit values are not 'WEEK' or just to say lets add few more visits say 'EOS' OR 'LOGS' in above data. How to select obs in that case.

many thanks in advance.

regards

ved

Super User
Posts: 10,018

Re: Subject selection based on if conditional processing.

That will output matched group with 'EOS' OR 'LOGS' .

Respected Advisor
Posts: 3,156

Re: Subject selection based on if conditional processing.

If you are certain your baseline is always there and have a VSBPSYS, you can simply count it. Otherwise, it needs small tweak. (Raw input is credited to )

data have;

infile cards truncover;

input (_USUBJID _VISNAME _PAGNAME VSBPSYS) ($);

cards;

01/001 DAY0 PAGE007 130

01/001 WEEK01 PAGE010

01/001 WEEK02 PAGE013

01/001 WEEK04 PAGE016

01/001 WEEK08 PAGE018

01/001 WEEK12 PAGE020

01/004 DAY0 PAGE007 130

01/004 WEEK01 PAGE010 130

01/004 WEEK02 PAGE013 149

01/004 WEEK04 PAGE016 144

02/003 DAY0 PAGE007 122

03/010 DAY0 PAGE007 129

03/010 WEEK01 PAGE010

03/010 WEEK02 PAGE013 135

;

run;

proc sql;

create table want as

select * from have

group by _USUBJID

having count(VSBPSYS) > 1;

quit;

Contributor ved
Contributor
Posts: 27

Re: Subject selection based on if conditional processing.

No , some times baseline visit is there but doesn't have the vsbpsys value . no all such cases where we have missing baseline value of vsbpsys , should not be included and must be eliminated from the dataset.( along with its subsequent missing post baseline values).

Respected Advisor
Posts: 3,156

Re: Subject selection based on if conditional processing.

Then a small tweak would do,

proc sql;

create table want as

select * from have

group by _USUBJID

having sum(_VISNAME='DAY0' AND NOT MISSING(VSBPSYS))=1 AND count(VSBPSYS) > 1;

quit;

Contributor ndp
Contributor
Posts: 61

Re: Subject selection based on if conditional processing.

since you ask for it here is a simpler code:

data have2 have3;

set have;

by _usubjid;

where nmiss(vbspsys);

if first.usubjid+last.usubjid=2 then output have3;

else output have2;

run;

this will remove all records with missing results.

Contributor ved
Contributor
Posts: 27

Re: Subject selection based on if conditional processing.

we don't need to remove missing records. records should be removed only for subjects, who don't have any post baseline  visits.

Contributor ndp
Contributor
Posts: 61

Re: Subject selection based on if conditional processing.

Ok Try this:

proc sort data have;

by id descending res vis page

run;

data have;

     set have;

     by id descending res vis page;

     retain count;

     if first.id then count=0;

     if not missing(res) and vis^="DAY0" then count=count+1;

     if count=0 and vis^="DAY0" then delete; **** remove post baseline records if all are misisng;

run;

data have;

     set have;

     by id;

     if first.id+last.id=2 then delete; *** remove baseline record; if no post baseline;

run;

run;

Ask a Question
Discussion stats
  • 13 replies
  • 390 views
  • 7 likes
  • 5 in conversation