BookmarkSubscribeRSS Feed
SASFREAK
Obsidian | Level 7

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.

13 REPLIES 13
SASFREAK
Obsidian | Level 7

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ksharp
Super User

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;
SASFREAK
Obsidian | Level 7

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

Ksharp
Super User

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

SASFREAK
Obsidian | Level 7

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

Ksharp
Super User

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

Haikuo
Onyx | Level 15

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;

SASFREAK
Obsidian | Level 7

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).

Haikuo
Onyx | Level 15

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;

ndp
Quartz | Level 8 ndp
Quartz | Level 8

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.

SASFREAK
Obsidian | Level 7

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

ndp
Quartz | Level 8 ndp
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 13 replies
  • 1691 views
  • 7 likes
  • 5 in conversation