BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
imcbczm
Obsidian | Level 7

Hi,

I have a dataset consists of patients id, date, disease status. Each patient has multiple observations with different dates. What I want is to only extract the observations WITHIN 30 days WITHIN a patient whenever the disease status is known. Could anybody help me to solve this? Thanks in advance!

Z

 

Example code:

----------------------------------------------

data have;

infile datalines missover;

input ID date anydtdte10. disease $ ;

datalines;

8657586 03/02/2011

8657586 03/12/2011

8657586 05/10/2011

8600380 08/01/2012 no

8600380 11/19/2012 no

8600380 11/23/2012 no

8600380 12/12/2012

8600380 04/05/2013

9519797 01/02/2013

9519797 02/01/2013

9519797 09/02/2013

9519797 09/12/2013

9519797 09/24/2013 yes

9519797 09/30/2013

9519797 12/03/2013

9519797 02/03/2014

;

run;

proc print data=have;

run;

 

/* I want this:  */

8600380 08/01/2012 no

8600380 11/19/2012 no

8600380 11/23/2012 no

8600380 12/12/2012

9519797 09/02/2013

9519797 09/12/2013

9519797 09/24/2013 yes

9519797 09/30/2013

9519797 12/03/2013

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Something like this, although your logic doesn't seem right, I don't see why a couple of your records would come out (you can get the from the code, there are three additional in your output)

 

proc sql;
  create table WANT as
  select  A.*
  from    HAVE A
  where exists(select distinct ID from HAVE where ID=A.ID and DATE <= A.DATE <= (DATE + 30) and DISEASE is not null);
quit;

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Something like this, although your logic doesn't seem right, I don't see why a couple of your records would come out (you can get the from the code, there are three additional in your output)

 

proc sql;
  create table WANT as
  select  A.*
  from    HAVE A
  where exists(select distinct ID from HAVE where ID=A.ID and DATE <= A.DATE <= (DATE + 30) and DISEASE is not null);
quit;
BrunoSilva
Quartz | Level 8

Hello,

 

I know that is not the answer Smiley Very Happy because i didn't understood correctly what you meen with 30 days. But......

Maybe it help you 

 

best regards,

 

/* order the data */
proc sort data=have;
	by id date;
run;

/* get the patient ids */
data idS;
	set have;
	by id date;

	format date prev_date date9. days 8.;
	retain confirmation 'N' total_days 0;

	if first.id then do;
		confirmation='N';
		total_days=0;
	end;

	prev_date=lag1(date);
	days=intck('DAY',prev_date,date);
	if strip(disease) ne '' then confirmation='Y';

	if confirmation eq 'Y' then total_days=sum(total_days,days);

	*if last.id and confirmation eq 'Y' and total_days ge 30 then output;

	*keep id;
run;

data want;
	merge ids(in=a) have(in=b);
	by id;
	if a and b;
run;

 

imcbczm
Obsidian | Level 7

This worked, thank you very much! I do want the observations 30 days prior to the known disease status, so i addes "DATE -30' to your code.

 

Z

ndp
Quartz | Level 8 ndp
Quartz | Level 8

Last record for 12/03 is not within 30 days of 09/24. Slightly lengthy version will give you all the rest:

proc sort data=have out=have1;
	by id date;
	where cmiss(id,disease)<=0;
run;

data have1;
	set have1;
	by id date;
	retain ord;
	if first.id then ord=0;
	ord=ord+1;
run;

proc transpose data=have1 out=have1t prefix=datex;
	by id;
	var date;
	id ord;
run;

data want;
	merge have(in=a) have1t(in=b);
	by id;
	if a and b;
	format date date10.;
run;

data want(drop=i datex: _:);
	set want;
	array temp{*} datex:;
	do i =1 to dim(temp);
		if nmiss(temp[i])=0 then do;
			if temp[i]-30<=date<=temp[i]+30 then output;
		end;
	end;
run;

proc sort data=want nodupkey;
	by id date disease;
run;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 5900 views
  • 1 like
  • 4 in conversation