Solved: Re: dates interruptions

Aljosa · Posted 03-05-2020 04:59 AM

PATIENT	VISITDT	INTENSITY	DATE_ONSET	DATE_END	term2
1	31DEC2015	Grade 1 (Mild)	.	.	lowering hemoglobin
1	31DEC2015	Grade 1 (Mild)	.	.	increase blood glucose
1	31DEC2015	Grade 4	.	.	hyperuricemia
1	31DEC2015	Grade 1 (Mild)	.	.	hypokalemia
1	18.janv.16	Grade 1 (Mild)	.	.	lowering hemoglobin
1	18.janv.16	Grade 1 (Mild)	.	.	increase AST
1	18.janv.16	Grade 3 (Severe)	.	.	hyperuricemia
1	18.janv.16	Grade 2 (Moderate)	.	.	hypokalemia
1	22.janv.16		.	.	lengthen about qtcf
1	22.janv.16	Grade 1 (Mild)	.	.	lowering blood calcium
1	15FEB2016	Grade 2 (Moderate)	.	.	athralgia
1	15FEB2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18FEB2016	Grade 1 (Mild)	.	.	hyperuricemia
1	18FEB2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	18FEB2016	Grade 2 (Moderate)	.	.	hypokalemia
1	23FEB2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	23FEB2016	Grade 1 (Mild)	.	.	increase AST
1	23FEB2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.mars.16	Grade 3 (Severe)	.	.	lowering hemoglobin
1	23.mars.16	Grade 1 (Mild)	.	.	increase AST
1	23.mars.16		.	.	lengthen about qtcf
1	23.mars.16	Grade 3 (Severe)	.	.	hyperuricemia
1	23.mars.16	Grade 1 (Mild)	.	.	hyperglycemia

Hi all,

In the table above you can see an example of patients reporting adverse events within different visits in hospitals. VISITDT is a visit date. term2 is adverse event verbatim. columns DATE_ONSET(=start date) and DATE_END are still not populated. I've chosen as example "hyperuricemia" to explain my problem. You can see that this patient #1 had hyperuricemia in all visits, except on the visit on 22.jan.2016.

Thus, in my final table I would like to have only 2 rows for hyperuricemia (including max INTENSITY):

1st row: term2=hyperuricemia DATE_ONSET=31.dec.2015 DATE_END=18.jan.2016 INTENSITY=Grade 4

2nd row: term2=hyperuricemia DATE_ONSET=15.feb.2016 DATE_END=23.mar.2016 INTENSITY=Grade 3 (Severe)

Basically I know how to have first and last date with proc sort, but my main problem is how to take into account the interruptions. Please could anyone help? I think program should look row by row, and if no hyperuricemia then it should take the previous VISITDT as end date. Then when program see hyperuricemia again it should take VISITDT as a new DATE_ONSET, and so on... THANK YOU!

Kurt_Bremser · Posted 03-10-2020 09:38 AM

It took me some time, but I think this is it:

data have;
infile cards dlm='09'x dsd truncover;
input patient :$10. visitdt :ddmmyy10. INTENSITY :$20. (date_onset date_end) (:ddmmyy10.) term2 :$30.;
format visitdt date_onset date_end ddmmyy10.;
datalines;
1	31.12.2015	Grade 1 (Mild)	.	.	lowering hemoglobin
1	31.12.2015	Grade 1 (Mild)	.	.	hypokalemia
1	31.12.2015	Grade 1 (Mild)	.	.	increase blood glucose
1	31.12.2015	Grade 4	.	.	hyperuricemia
1	18.01.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	18.01.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18.01.2016	Grade 1 (Mild)	.	.	increase AST
1	18.01.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	22.01.2016		.	.	lengthen about qtcf
1	22.01.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	15.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	15.02.2016	Grade 2 (Moderate)	.	.	athralgia
1	18.02.2016	Grade 1 (Mild)	.	.	hyperuricemia
1	18.02.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	18.02.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	23.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.02.2016	Grade 1 (Mild)	.	.	increase AST
1	23.02.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	23.03.2016	Grade 1 (Mild)	.	.	hyperglycemia
1	23.03.2016		.	.	lengthen about qtcf
1	23.03.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.03.2016	Grade 3 (Severe)	.	.	hypokalemia
1	23.03.2016	Grade 1 (Mild)	.	.	increase AST
1	23.03.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	21.04.2016	Grade 2 (Moderate)	.	.	hyperuricemia
1	21.04.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	21.04.2016	Grade 1 (Mild)	.	.	hypokalemia
1	19.05.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	19.05.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	16.06.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	16.06.2016	Grade 1 (Mild)	.	.	increase blood glucose
1	16.06.2016	Grade 1 (Mild)	.	.	increase AST
1	16.06.2016	Grade 4	.	.	hyperuricemia
1	19.07.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	19.07.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	19.07.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	19.07.2016	Grade 1 (Mild)	.	.	ALT increase
1	19.07.2016	Grade 2 (Moderate)	.	.	increase blood glucose
1	19.07.2016	Grade 1 (Mild)	.	.	increase AST
1	16.08.2016	Grade 1 (Mild)	.	.	hypokalemia
1	16.08.2016	Grade 1 (Mild)	.	.	hyperuricemia
1	16.08.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	16.08.2016		.	.	lengthen about qtcf
1	03.10.2016		.	.	lengthen about qtcf
1	02.11.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	03.11.2016		.	.	lengthen about qtcf
1	22.11.2016	Grade 1 (Mild)	.	.	hypokalemia
1	22.11.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	22.11.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	01.12.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	01.12.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	01.12.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	22.12.2016	Grade 1 (Mild)	.	.	brown skin
1	22.12.2016	Grade 1 (Mild)	.	.	increase blood glucose
1	22.12.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	22.12.2016	Grade 1 (Mild)	.	.	increase AST
1	22.12.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	19.01.2017	Grade 3 (Severe)	.	.	hyperuricemia
1	19.01.2017	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	19.01.2017	Grade 1 (Mild)	.	.	increase blood glucose
1	19.01.2017	Grade 2 (Moderate)	.	.	hypokalemia
1	22.02.2017	Grade 2 (Moderate)	.	.	hypokalemia
1	22.02.2017	Grade 1 (Mild)	.	.	ALT increase
1	22.02.2017	Grade 1 (Mild)	.	.	lowering hemoglobin
1	22.02.2017	Grade 2 (Moderate)	.	.	increase blood glucose
1	22.02.2017	Grade 2 (Moderate)	.	.	increase AST
1	20.04.2017		.	.	lengthen about qtcf
1	23.05.2017		.	.	lengthen about qtcf
1	23.05.2017	Grade 2 (Moderate)	.	.	increase blood glucose
1	23.05.2017	Grade 3 (Severe)	.	.	hepatitis
1	23.05.2017	Grade 1 (Mild)	.	.	hyperuricemia
1	23.05.2017	Grade 2 (Moderate)	.	.	hypokalemia
1	22.06.2017		.	.	lengthen about qtcf
1	22.08.2017	Grade 2 (Moderate)	.	.	hypokalemia
;

/* create all visit dates for a patient */
proc sort
  data=have (keep=patient visitdt)
  out=all_dates
  nodupkey
;
by patient visitdt;
run;

proc sort data=have;
by patient visitdt term2;
run;

/* create a cartesian join, so we have all term2's for all dates */
proc sql;
create table all_dates_terms as
select distinct a.patient, a.visitdt, b.term2
from all_dates a, have b
where a.patient = b.patient;
quit;

/* join that back with have, but take intensity from there */
data all_have;
merge
  all_dates_terms (in=a)
  have (drop=date_onset date_end)
;
by patient visitdt term2;
if a;
run;

/* move term2 up in the sort order */
proc sort data=all_have;
by patient term2 visitdt;
run;

/* and now get the result */
data want (
  keep=patient term2 int date_onset date_end
  rename=(int=intensity)
);
set all_have;
by patient term2;
retain int date_onset date_end;
format date_onset date_end ddmmyyp10.;
if first.term2 then date_onset = .;
if intensity > '' and date_onset = . then date_onset = visitdt;
if intensity > int then int = intensity;
if intensity = ''
then do;
  if date_onset ne . then output;
  date_onset = .;
  int = '';
end;
date_end = visitdt;
if last.term2 and date_onset ne . and intensity > '' then output;
run;

proc print data=want noobs;
run;

Result:

patient    term2                     intensity             date_onset      date_end

   1       ALT increase              Grade 1 (Mild)        19.07.2016    19.07.2016
   1       ALT increase              Grade 1 (Mild)        22.02.2017    22.02.2017
   1       athralgia                 Grade 2 (Moderate)    15.02.2016    15.02.2016
   1       brown skin                Grade 1 (Mild)        22.12.2016    22.12.2016
   1       hepatitis                 Grade 3 (Severe)      23.05.2017    23.05.2017
   1       hyperglycemia             Grade 1 (Mild)        23.03.2016    23.03.2016
   1       hyperuricemia             Grade 4               31.12.2015    18.01.2016
   1       hyperuricemia             Grade 4               15.02.2016    16.08.2016
   1       hyperuricemia             Grade 3 (Severe)      22.11.2016    19.01.2017
   1       hyperuricemia             Grade 1 (Mild)        23.05.2017    23.05.2017
   1       hypokalemia               Grade 2 (Moderate)    31.12.2015    18.01.2016
   1       hypokalemia               Grade 2 (Moderate)    18.02.2016    18.02.2016
   1       hypokalemia               Grade 3 (Severe)      23.03.2016    21.04.2016
   1       hypokalemia               Grade 2 (Moderate)    19.07.2016    16.08.2016
   1       hypokalemia               Grade 1 (Mild)        22.11.2016    22.11.2016
   1       hypokalemia               Grade 2 (Moderate)    19.01.2017    22.02.2017
   1       hypokalemia               Grade 2 (Moderate)    23.05.2017    23.05.2017
   1       hypokalemia               Grade 2 (Moderate)    22.08.2017    22.08.2017
   1       increase AST              Grade 1 (Mild)        18.01.2016    18.01.2016
   1       increase AST              Grade 1 (Mild)        23.02.2016    23.03.2016
   1       increase AST              Grade 1 (Mild)        16.06.2016    19.07.2016
   1       increase AST              Grade 1 (Mild)        22.12.2016    22.12.2016
   1       increase AST              Grade 2 (Moderate)    22.02.2017    22.02.2017
   1       increase blood glucose    Grade 1 (Mild)        31.12.2015    31.12.2015
   1       increase blood glucose    Grade 2 (Moderate)    16.06.2016    19.07.2016
   1       increase blood glucose    Grade 2 (Moderate)    22.12.2016    22.02.2017
   1       increase blood glucose    Grade 2 (Moderate)    23.05.2017    23.05.2017
   1       lowering blood calcium    Grade 1 (Mild)        22.01.2016    22.01.2016
   1       lowering blood calcium    Grade 1 (Mild)        18.02.2016    18.02.2016
   1       lowering blood calcium    Grade 1 (Mild)        01.12.2016    01.12.2016
   1       lowering hemoglobin       Grade 1 (Mild)        31.12.2015    18.01.2016
   1       lowering hemoglobin       Grade 3 (Severe)      23.02.2016    16.08.2016
   1       lowering hemoglobin       Grade 3 (Severe)      02.11.2016    02.11.2016
   1       lowering hemoglobin       Grade 2 (Moderate)    22.11.2016    22.02.2017

For the three terms you gave as example, the results match.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

View solution in original post

Kurt_Bremser · Posted 03-05-2020 05:39 AM

@Aljosa wrote:
PATIENT	VISITDT	INTENSITY	DATE_ONSET	DATE_END	term2
1	31DEC2015	Grade 1 (Mild)	.	.	lowering hemoglobin
1	31DEC2015	Grade 1 (Mild)	.	.	increase blood glucose
1	31DEC2015	Grade 4	.	.	hyperuricemia
1	31DEC2015	Grade 1 (Mild)	.	.	hypokalemia
1	18.janv.16	Grade 1 (Mild)	.	.	lowering hemoglobin
1	18.janv.16	Grade 1 (Mild)	.	.	increase AST
1	18.janv.16	Grade 3 (Severe)	.	.	hyperuricemia
1	18.janv.16	Grade 2 (Moderate)	.	.	hypokalemia
1	22.janv.16		.	.	lengthen about qtcf
1	22.janv.16	Grade 1 (Mild)	.	.	lowering blood calcium
1	15FEB2016	Grade 2 (Moderate)	.	.	athralgia
1	15FEB2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18FEB2016	Grade 1 (Mild)	.	.	hyperuricemia
1	18FEB2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	18FEB2016	Grade 2 (Moderate)	.	.	hypokalemia
1	23FEB2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	23FEB2016	Grade 1 (Mild)	.	.	increase AST
1	23FEB2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.mars.16	Grade 3 (Severe)	.	.	lowering hemoglobin
1	23.mars.16	Grade 1 (Mild)	.	.	increase AST
1	23.mars.16		.	.	lengthen about qtcf
1	23.mars.16	Grade 3 (Severe)	.	.	hyperuricemia
1	23.mars.16	Grade 1 (Mild)	.	.	hyperglycemia
Hi all,

In the table above you can see an example of patients reporting adverse events within different visits in hospitals. VISITDT is a visit date. term2 is adverse event verbatim. columns DATE_ONSET(=start date) and DATE_END are still not populated. I've chosen as example "hyperuricemia" to explain my problem. You can see that this patient #1 had hyperuricemia in all visits, except on the visit on 22.jan.2016.

Thus, in my final table I would like to have only 2 rows for hyperuricemia (including max INTENSITY):

1st row: term2=hyperuricemia DATE_ONSET=31.dec.2015 DATE_END=18.jan.2016 INTENSITY=Grade 4

2nd row: term2=hyperuricemia DATE_ONSET=15.feb.2016 DATE_END=23.mar.2016 INTENSITY=Grade 3 (Severe)

Basically I know how to have first and last date with proc sort, but my main problem is how to take into account the interruptions. Please could anyone help? I think program should look row by row, and if no hyperuricemia then it should take the previous VISITDT as end date. Then when program see hyperuricemia again it should take VISITDT as a new DATE_ONSET, and so on... THANK YOU!

Please supply example data in a WORKING DATA STEP WITH DATALINES, as you have been shown by @mkeintz in https://communities.sas.com/t5/SAS-Programming/continuous-date/m-p/601899. Right now, your dates can not be imported with a default SAS informat.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Aljosa · Posted 03-05-2020 08:34 AM

How to do it? I copy my spreadsheet here in text? It doesnt work. I dont remember how I did it last time. Thank you.

Kurt_Bremser · Posted 03-05-2020 08:38 AM

Follow the link I posted previously; there you see a working data step with dates and even strings that contain blanks.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Aljosa · Posted 03-05-2020 09:21 AM

1	31DEC2015	Grade 1 (Mild)	.	.	lowering hemoglobin
1	31DEC2015	Grade 1 (Mild)	.	.	increase blood glucose
1	31DEC2015	Grade 4	.	.	hyperuricemia
1	31DEC2015	Grade 1 (Mild)	.	.	hypokalemia
1	18.01.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	18.01.2016	Grade 1 (Mild)	.	.	increase AST
1	18.01.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18.01.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	22.01.2016		.	.	lengthen about qtcf
1	22.01.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	15FEB2016	Grade 2 (Moderate)	.	.	athralgia
1	15FEB2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18FEB2016	Grade 1 (Mild)	.	.	hyperuricemia
1	18FEB2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	18FEB2016	Grade 2 (Moderate)	.	.	hypokalemia
1	23FEB2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	23FEB2016	Grade 1 (Mild)	.	.	increase AST
1	23FEB2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.03.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	23.03.2016	Grade 1 (Mild)	.	.	increase AST
1	23.03.2016		.	.	lengthen about qtcf
1	23.03.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.03.2016	Grade 1 (Mild)	.	.	hyperglycemia

Like this? Here you have dates and blanks...

I used "insert SAS code" tool

Regards

Aljosa · Posted 03-05-2020 09:57 AM

data have;
  infile cards expandtabs truncover;
  input patient	$100.	visitdt (:ddmmyy10.) INTENSITY $100. (date_onset date_end) (:ddmmyy10.) term2 $100.;
  format visitdt date_onset	date_end ddmmyy10.;
datalines;
1	31.12.2015	Grade 1 (Mild)	.	.	lowering hemoglobin
1	31.12.2015	Grade 1 (Mild)	.	.	increase blood glucose
1	31.12.2015	Grade 4	.	.	hyperuricemia
1	31.12.2015	Grade 1 (Mild)	.	.	hypokalemia
1	18.01.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	18.01.2016	Grade 1 (Mild)	.	.	increase AST
1	18.01.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18.01.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	22.01.2016		.	.	lengthen about qtcf
1	22.01.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	15.02.2016	Grade 2 (Moderate)	.	.	athralgia
1	15.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18.02.2016	Grade 1 (Mild)	.	.	hyperuricemia
1	18.02.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	18.02.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	23.02.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	23.02.2016	Grade 1 (Mild)	.	.	increase AST
1	23.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.03.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	23.03.2016	Grade 1 (Mild)	.	.	increase AST
1	23.03.2016		.	.	lengthen about qtcf
1	23.03.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.03.2016	Grade 1 (Mild)	.	.	hyperglycemia

run;

Like this?

Kurt_Bremser · Posted 03-05-2020 10:46 AM

Almost:

data have;
infile cards dlm='09'x dsd truncover;
input patient :$10. visitdt :ddmmyy10. INTENSITY :$20. (date_onset date_end) (:ddmmyy10.) term2 :$30.;
format visitdt date_onset date_end ddmmyy10.;
datalines;
1	31.12.2015	Grade 1 (Mild)	.	.	lowering hemoglobin
1	31.12.2015	Grade 1 (Mild)	.	.	increase blood glucose
1	31.12.2015	Grade 4	.	.	hyperuricemia
1	31.12.2015	Grade 1 (Mild)	.	.	hypokalemia
1	18.01.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	18.01.2016	Grade 1 (Mild)	.	.	increase AST
1	18.01.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18.01.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	22.01.2016		.	.	lengthen about qtcf
1	22.01.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	15.02.2016	Grade 2 (Moderate)	.	.	athralgia
1	15.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18.02.2016	Grade 1 (Mild)	.	.	hyperuricemia
1	18.02.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	18.02.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	23.02.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	23.02.2016	Grade 1 (Mild)	.	.	increase AST
1	23.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.03.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	23.03.2016	Grade 1 (Mild)	.	.	increase AST
1	23.03.2016		.	.	lengthen about qtcf
1	23.03.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.03.2016	Grade 1 (Mild)	.	.	hyperglycemia
;

Since your text block contains tabs as separators(delimiters), we can use those. The dsd option lets SAS recognize a missing character value, so we now get a nice dataset. Note that a datalines block ends with a line with a single semicolon; this semicolon must immediately follow the final dataline, otherwise you get one or more empty observation(s). Since the datalines block ends the data step per definition, no run; statement is needed.

Try this:

%let searchterm=hyperuricemia;
options mergenoby=nowarn;

data want;
set have (drop=date_onset date_end);
by patient visitdt;
retain date_onset date_end max_grade term_found;
format date_onset date_end ddmmyy10. max_grade $20.;
if first.patient then do;
  date_onset = .;
  date_end = .;
  max_grade = '';
end;
if first.visitdt then term_found = 0;
if term2 = "&searchterm" then do;
  if date_onset = . then date_onset = visitdt;
  term_found = 1;
  if intensity > max_grade then max_grade = intensity;
end;
if last.visitdt and date_onset ne . then do;
  if term_found then date_end = visitdt;
  else do;
    output;
    date_end = visitdt;
    date_onset = .;
    max_grade = '';
  end;
end;
if last.patient and date_onset ne . then do;
  date_end = visitdt;
  output;
end;
keep patient date_onset date_end max_grade;
run;

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Aljosa · Posted 03-06-2020 02:48 AM

Hello Kurt,

It doesnt work, as I get the table without adverse events.

I would need a table summarizing, per patient, each adverse event with its start and end date, taking into account interruptions (example for hyperuricemia, the event was interrupted on 22.jan.2016).

Kind regards.

Kurt_Bremser · Posted 03-06-2020 03:40 AM

I tested my code. It creates exactly the two rows you mentioned in your initial post.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Aljosa · Posted 03-06-2020 09:19 AM

Hi,

when I run the table "have" I get this (so, I have a problem somewhere):

If however I run your main program directly on my original table with all patients, I get this:

(Note that in your main program I added only "term2" in instruction keep at the end)

So, I see only 4 rows for patient number 1, and no hyperuricemia there...

In the program below I put all events for the patient number 1:

data have;
infile cards dlm='09'x dsd truncover;
input patient :$10. visitdt :ddmmyy10. INTENSITY :$20. (date_onset date_end) (:ddmmyy10.) term2 :$30.;
format visitdt date_onset date_end ddmmyy10.;
datalines;
1	31.12.2015	Grade 1 (Mild)	.	.	lowering hemoglobin
1	31.12.2015	Grade 1 (Mild)	.	.	hypokalemia
1	31.12.2015	Grade 1 (Mild)	.	.	increase blood glucose
1	31.12.2015	Grade 4	.	.	hyperuricemia
1	18.01.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	18.01.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18.01.2016	Grade 1 (Mild)	.	.	increase AST
1	18.01.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	22.01.2016		.	.	lengthen about qtcf
1	22.01.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	15.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	15.02.2016	Grade 2 (Moderate)	.	.	athralgia
1	18.02.2016	Grade 1 (Mild)	.	.	hyperuricemia
1	18.02.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	18.02.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	23.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.02.2016	Grade 1 (Mild)	.	.	increase AST
1	23.02.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	23.03.2016	Grade 1 (Mild)	.	.	hyperglycemia
1	23.03.2016		.	.	lengthen about qtcf
1	23.03.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.03.2016	Grade 3 (Severe)	.	.	hypokalemia
1	23.03.2016	Grade 1 (Mild)	.	.	increase AST
1	23.03.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	21.04.2016	Grade 2 (Moderate)	.	.	hyperuricemia
1	21.04.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	21.04.2016	Grade 1 (Mild)	.	.	hypokalemia
1	19.05.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	19.05.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	16.06.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	16.06.2016	Grade 1 (Mild)	.	.	increase blood glucose
1	16.06.2016	Grade 1 (Mild)	.	.	increase AST
1	16.06.2016	Grade 4	.	.	hyperuricemia
1	19.07.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	19.07.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	19.07.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	19.07.2016	Grade 1 (Mild)	.	.	ALT increase
1	19.07.2016	Grade 2 (Moderate)	.	.	increase blood glucose
1	19.07.2016	Grade 1 (Mild)	.	.	increase AST
1	16.08.2016	Grade 1 (Mild)	.	.	hypokalemia
1	16.08.2016	Grade 1 (Mild)	.	.	hyperuricemia
1	16.08.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	16.08.2016		.	.	lengthen about qtcf
1	03.10.2016		.	.	lengthen about qtcf
1	02.11.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	03.11.2016		.	.	lengthen about qtcf
1	22.11.2016	Grade 1 (Mild)	.	.	hypokalemia
1	22.11.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	22.11.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	01.12.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	01.12.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	01.12.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	22.12.2016	Grade 1 (Mild)	.	.	brown skin
1	22.12.2016	Grade 1 (Mild)	.	.	increase blood glucose
1	22.12.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	22.12.2016	Grade 1 (Mild)	.	.	increase AST
1	22.12.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	19.01.2017	Grade 3 (Severe)	.	.	hyperuricemia
1	19.01.2017	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	19.01.2017	Grade 1 (Mild)	.	.	increase blood glucose
1	19.01.2017	Grade 2 (Moderate)	.	.	hypokalemia
1	22.02.2017	Grade 2 (Moderate)	.	.	hypokalemia
1	22.02.2017	Grade 1 (Mild)	.	.	ALT increase
1	22.02.2017	Grade 1 (Mild)	.	.	lowering hemoglobin
1	22.02.2017	Grade 2 (Moderate)	.	.	increase blood glucose
1	22.02.2017	Grade 2 (Moderate)	.	.	increase AST
1	20.04.2017		.	.	lengthen about qtcf
1	23.05.2017		.	.	lengthen about qtcf
1	23.05.2017	Grade 2 (Moderate)	.	.	increase blood glucose
1	23.05.2017	Grade 3 (Severe)	.	.	hepatitis
1	23.05.2017	Grade 1 (Mild)	.	.	hyperuricemia
1	23.05.2017	Grade 2 (Moderate)	.	.	hypokalemia
1	22.06.2017		.	.	lengthen about qtcf
1	22.08.2017	Grade 2 (Moderate)	.	.	hypokalemia
;

Using this dataset above, I would like in my final table my rows start like this:

In this final table above I describe only 3 events how it should look like. The variable VISITDT is populating DATE_ONSET and DATE_END, and when there is adverse event (term2) interruption, it creates another row for the event. and so on...

Thank you.

Kind regards.

Aljosa

Kurt_Bremser · Posted 03-10-2020 09:38 AM

It took me some time, but I think this is it:

data have;
infile cards dlm='09'x dsd truncover;
input patient :$10. visitdt :ddmmyy10. INTENSITY :$20. (date_onset date_end) (:ddmmyy10.) term2 :$30.;
format visitdt date_onset date_end ddmmyy10.;
datalines;
1	31.12.2015	Grade 1 (Mild)	.	.	lowering hemoglobin
1	31.12.2015	Grade 1 (Mild)	.	.	hypokalemia
1	31.12.2015	Grade 1 (Mild)	.	.	increase blood glucose
1	31.12.2015	Grade 4	.	.	hyperuricemia
1	18.01.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	18.01.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	18.01.2016	Grade 1 (Mild)	.	.	increase AST
1	18.01.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	22.01.2016		.	.	lengthen about qtcf
1	22.01.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	15.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	15.02.2016	Grade 2 (Moderate)	.	.	athralgia
1	18.02.2016	Grade 1 (Mild)	.	.	hyperuricemia
1	18.02.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	18.02.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	23.02.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.02.2016	Grade 1 (Mild)	.	.	increase AST
1	23.02.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	23.03.2016	Grade 1 (Mild)	.	.	hyperglycemia
1	23.03.2016		.	.	lengthen about qtcf
1	23.03.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	23.03.2016	Grade 3 (Severe)	.	.	hypokalemia
1	23.03.2016	Grade 1 (Mild)	.	.	increase AST
1	23.03.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	21.04.2016	Grade 2 (Moderate)	.	.	hyperuricemia
1	21.04.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	21.04.2016	Grade 1 (Mild)	.	.	hypokalemia
1	19.05.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	19.05.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	16.06.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	16.06.2016	Grade 1 (Mild)	.	.	increase blood glucose
1	16.06.2016	Grade 1 (Mild)	.	.	increase AST
1	16.06.2016	Grade 4	.	.	hyperuricemia
1	19.07.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	19.07.2016	Grade 2 (Moderate)	.	.	hypokalemia
1	19.07.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	19.07.2016	Grade 1 (Mild)	.	.	ALT increase
1	19.07.2016	Grade 2 (Moderate)	.	.	increase blood glucose
1	19.07.2016	Grade 1 (Mild)	.	.	increase AST
1	16.08.2016	Grade 1 (Mild)	.	.	hypokalemia
1	16.08.2016	Grade 1 (Mild)	.	.	hyperuricemia
1	16.08.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	16.08.2016		.	.	lengthen about qtcf
1	03.10.2016		.	.	lengthen about qtcf
1	02.11.2016	Grade 3 (Severe)	.	.	lowering hemoglobin
1	03.11.2016		.	.	lengthen about qtcf
1	22.11.2016	Grade 1 (Mild)	.	.	hypokalemia
1	22.11.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	22.11.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	01.12.2016	Grade 1 (Mild)	.	.	lowering blood calcium
1	01.12.2016	Grade 1 (Mild)	.	.	lowering hemoglobin
1	01.12.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	22.12.2016	Grade 1 (Mild)	.	.	brown skin
1	22.12.2016	Grade 1 (Mild)	.	.	increase blood glucose
1	22.12.2016	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	22.12.2016	Grade 1 (Mild)	.	.	increase AST
1	22.12.2016	Grade 3 (Severe)	.	.	hyperuricemia
1	19.01.2017	Grade 3 (Severe)	.	.	hyperuricemia
1	19.01.2017	Grade 2 (Moderate)	.	.	lowering hemoglobin
1	19.01.2017	Grade 1 (Mild)	.	.	increase blood glucose
1	19.01.2017	Grade 2 (Moderate)	.	.	hypokalemia
1	22.02.2017	Grade 2 (Moderate)	.	.	hypokalemia
1	22.02.2017	Grade 1 (Mild)	.	.	ALT increase
1	22.02.2017	Grade 1 (Mild)	.	.	lowering hemoglobin
1	22.02.2017	Grade 2 (Moderate)	.	.	increase blood glucose
1	22.02.2017	Grade 2 (Moderate)	.	.	increase AST
1	20.04.2017		.	.	lengthen about qtcf
1	23.05.2017		.	.	lengthen about qtcf
1	23.05.2017	Grade 2 (Moderate)	.	.	increase blood glucose
1	23.05.2017	Grade 3 (Severe)	.	.	hepatitis
1	23.05.2017	Grade 1 (Mild)	.	.	hyperuricemia
1	23.05.2017	Grade 2 (Moderate)	.	.	hypokalemia
1	22.06.2017		.	.	lengthen about qtcf
1	22.08.2017	Grade 2 (Moderate)	.	.	hypokalemia
;

/* create all visit dates for a patient */
proc sort
  data=have (keep=patient visitdt)
  out=all_dates
  nodupkey
;
by patient visitdt;
run;

proc sort data=have;
by patient visitdt term2;
run;

/* create a cartesian join, so we have all term2's for all dates */
proc sql;
create table all_dates_terms as
select distinct a.patient, a.visitdt, b.term2
from all_dates a, have b
where a.patient = b.patient;
quit;

/* join that back with have, but take intensity from there */
data all_have;
merge
  all_dates_terms (in=a)
  have (drop=date_onset date_end)
;
by patient visitdt term2;
if a;
run;

/* move term2 up in the sort order */
proc sort data=all_have;
by patient term2 visitdt;
run;

/* and now get the result */
data want (
  keep=patient term2 int date_onset date_end
  rename=(int=intensity)
);
set all_have;
by patient term2;
retain int date_onset date_end;
format date_onset date_end ddmmyyp10.;
if first.term2 then date_onset = .;
if intensity > '' and date_onset = . then date_onset = visitdt;
if intensity > int then int = intensity;
if intensity = ''
then do;
  if date_onset ne . then output;
  date_onset = .;
  int = '';
end;
date_end = visitdt;
if last.term2 and date_onset ne . and intensity > '' then output;
run;

proc print data=want noobs;
run;

Result:

patient    term2                     intensity             date_onset      date_end

   1       ALT increase              Grade 1 (Mild)        19.07.2016    19.07.2016
   1       ALT increase              Grade 1 (Mild)        22.02.2017    22.02.2017
   1       athralgia                 Grade 2 (Moderate)    15.02.2016    15.02.2016
   1       brown skin                Grade 1 (Mild)        22.12.2016    22.12.2016
   1       hepatitis                 Grade 3 (Severe)      23.05.2017    23.05.2017
   1       hyperglycemia             Grade 1 (Mild)        23.03.2016    23.03.2016
   1       hyperuricemia             Grade 4               31.12.2015    18.01.2016
   1       hyperuricemia             Grade 4               15.02.2016    16.08.2016
   1       hyperuricemia             Grade 3 (Severe)      22.11.2016    19.01.2017
   1       hyperuricemia             Grade 1 (Mild)        23.05.2017    23.05.2017
   1       hypokalemia               Grade 2 (Moderate)    31.12.2015    18.01.2016
   1       hypokalemia               Grade 2 (Moderate)    18.02.2016    18.02.2016
   1       hypokalemia               Grade 3 (Severe)      23.03.2016    21.04.2016
   1       hypokalemia               Grade 2 (Moderate)    19.07.2016    16.08.2016
   1       hypokalemia               Grade 1 (Mild)        22.11.2016    22.11.2016
   1       hypokalemia               Grade 2 (Moderate)    19.01.2017    22.02.2017
   1       hypokalemia               Grade 2 (Moderate)    23.05.2017    23.05.2017
   1       hypokalemia               Grade 2 (Moderate)    22.08.2017    22.08.2017
   1       increase AST              Grade 1 (Mild)        18.01.2016    18.01.2016
   1       increase AST              Grade 1 (Mild)        23.02.2016    23.03.2016
   1       increase AST              Grade 1 (Mild)        16.06.2016    19.07.2016
   1       increase AST              Grade 1 (Mild)        22.12.2016    22.12.2016
   1       increase AST              Grade 2 (Moderate)    22.02.2017    22.02.2017
   1       increase blood glucose    Grade 1 (Mild)        31.12.2015    31.12.2015
   1       increase blood glucose    Grade 2 (Moderate)    16.06.2016    19.07.2016
   1       increase blood glucose    Grade 2 (Moderate)    22.12.2016    22.02.2017
   1       increase blood glucose    Grade 2 (Moderate)    23.05.2017    23.05.2017
   1       lowering blood calcium    Grade 1 (Mild)        22.01.2016    22.01.2016
   1       lowering blood calcium    Grade 1 (Mild)        18.02.2016    18.02.2016
   1       lowering blood calcium    Grade 1 (Mild)        01.12.2016    01.12.2016
   1       lowering hemoglobin       Grade 1 (Mild)        31.12.2015    18.01.2016
   1       lowering hemoglobin       Grade 3 (Severe)      23.02.2016    16.08.2016
   1       lowering hemoglobin       Grade 3 (Severe)      02.11.2016    02.11.2016
   1       lowering hemoglobin       Grade 2 (Moderate)    22.11.2016    22.02.2017

For the three terms you gave as example, the results match.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Aljosa · Posted 03-10-2020 10:05 AM

OMG this works!!! Its amazing ;)))) You are my life saver!!! Thank you million times dear Kurt!!! Have a wonderful day++++++++

SAS Innovate 2025: Register Now

SAS Training: Just a Click Away