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

Here is the data I have

data have;
input id $ treatment $ linenumber date yymmdd10. ;
format date yymmdd10.;
cards;
001 A 1 2014-08-15
001 A 1 2015-06-20
001 A 2 2015-08-29
001 A 2 2018-02-28
001 A 2 2018-05-07
001 B 3 2018-08-01
001 B 4 2019-02-01 
001 C 5 2019-03-03
;
run;

I want to calculate the gap between the dates, and then create two flags:

within the same ID/Treatment/Linenumber, if gap >90 days then GT90_within_line =1;

within the same ID/Treatment, when linenumber changes, if the gap < 90 days, then LT90_btw_line_within_trt = 1

 

The expected output data will be like this:

Capture.PNG

 

Can anyone help? Many thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
fengyuwuzu
Pyrite | Level 9

I have a solution but wonder if there is simpler methods

 

data have;
input id $ treatment $ linenumber date yymmdd10. ;
format date yymmdd10.;
cards;
001 A 1 2014-08-15
001 A 1 2015-06-20
001 A 2 2015-08-29
001 A 2 2018-02-28
001 A 2 2018-05-07
001 B 3 2018-08-01
001 B 4 2019-02-01 
001 C 5 2019-03-03
002 A 1 2015-06-27
002 A 1 2015-09-02
002 A 2 2016-01-01
;
run;
proc sort data=have;
	by id treatment linenumber date;
run;

data want;
	set have;
	by id treatment linenumber date;
	gap=dif(date);

	if first.id then
		gap=.;
run;

proc sql;
	create table step1 as 
		select *, case when count(date)>=2 and gap > 90 then 1 else . end as GT90_within_line
			from want
				group by id, treatment, linenumber
					order by id, treatment, linenumber, date;
quit;

data step2;
	set step1;
	by id treatment linenumber date;

	if not first.treatment and first.linenumber and . < gap <90 then
		LT90_btw_line_within_trt =1;
run;

 

View solution in original post

3 REPLIES 3
fengyuwuzu
Pyrite | Level 9

I have a solution but wonder if there is simpler methods

 

data have;
input id $ treatment $ linenumber date yymmdd10. ;
format date yymmdd10.;
cards;
001 A 1 2014-08-15
001 A 1 2015-06-20
001 A 2 2015-08-29
001 A 2 2018-02-28
001 A 2 2018-05-07
001 B 3 2018-08-01
001 B 4 2019-02-01 
001 C 5 2019-03-03
002 A 1 2015-06-27
002 A 1 2015-09-02
002 A 2 2016-01-01
;
run;
proc sort data=have;
	by id treatment linenumber date;
run;

data want;
	set have;
	by id treatment linenumber date;
	gap=dif(date);

	if first.id then
		gap=.;
run;

proc sql;
	create table step1 as 
		select *, case when count(date)>=2 and gap > 90 then 1 else . end as GT90_within_line
			from want
				group by id, treatment, linenumber
					order by id, treatment, linenumber, date;
quit;

data step2;
	set step1;
	by id treatment linenumber date;

	if not first.treatment and first.linenumber and . < gap <90 then
		LT90_btw_line_within_trt =1;
run;

 

ErikLund_Jensen
Rhodochrosite | Level 12

Hi @fengyuwuzu 

 

It can be done in one data step using lag function, if the input data set is sorted on ID Treatment Linenumber Date.

 

data want (drop=oID oTreatment oDate); 
	set have; by ID Treatment Linenumber;
	oID = lag(ID);
	oTreatment = lag(Treatment);
	oLinenumber = lag(Linenumber);
	oDate = lag(Date);
	if not first.ID then Gap = Date-oDate;
	if not first.Linenumber then do;
		if Gap > 90 then GT90_within_line = 1;
	end;
	else if not first.Treatment then if Gap < 90 then LT90_btw_line_within_trt = 1;
run;

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
  • 3 replies
  • 407 views
  • 0 likes
  • 2 in conversation