Checking records for missing values at subsequent visits

[PSU_Sudzi]

I have a dataset with a layout as in the table below and I am trying to find a way to check if values are missing for TLIDIA (longest diameter) at subsequent visits when they are present at Visit 1. For example, since _RECNUM (lesions) 1 and 2 each have a measurement, those same lesions should have a TLIDIA present throughout the study. Same with PLARES (grade of lesion). In the table below there aren't any records that meet that criteria. Any idea how to get this started with a datastep or proc sql? Many thanks!

_usubjid	visnum	_visname	date	_recnum	tlidia	tlithk	tlina	plares	plana
01/001	10	VISIT01	31-Jan-19	1	4	1		2
01/001	10	VISIT01	31-Jan-19	2	3	1	0	2	0
01/001	10	VISIT01	31-Jan-19	3			1		1
01/001	10	VISIT01	31-Jan-19	4			1		1
01/001	10	VISIT01	31-Jan-19	5			1		1
01/001	10	VISIT01	31-Jan-19	6			1		1
01/001	20	VISIT02	7-Feb-19	1	4	1		2
01/001	20	VISIT02	7-Feb-19	2	3	1	0	2	0
01/001	20	VISIT02	7-Feb-19	3			1		1
01/001	20	VISIT02	7-Feb-19	4			1		1
01/001	20	VISIT02	7-Feb-19	5			1		1
01/001	20	VISIT02	7-Feb-19	6			1		1
01/001	30	VISIT03	14-Feb-19	1	3			2
01/001	30	VISIT03	14-Feb-19	2	3		0	2	0
01/001	30	VISIT03	14-Feb-19	3			1		1
01/001	30	VISIT03	14-Feb-19	4			1		1
01/001	30	VISIT03	14-Feb-19	5			1		1
01/001	30	VISIT03	14-Feb-19	6			1		1

[ballardw]

It might help to show what you actually want for output but I am guessing this might get you started (not tested as no data provided)

proc sql;
   create table wanttlidia as
   select b.* 
   from (select * from have 
         where not missing(tlidia)) as a
         left join
         have as b
         on a._usubjid=b._usubjid
         and a._recnum=b._recnum
   where b.visnum > a.visnum
   ;
run;

It is better to provide data in the form of a data step to avoid having to answer questions about variable types and properties.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

You would want to replace any sensitive personal information will appropriate dummy values like 11111 or XXXXX

[PSU_Sudzi]

Thank you for your prompt response earlier and pointing me in the direction of posting a data step here, I was actually looking for that post and have now bookmarked it.

So as far as output goes, I am looking to see a list of those records that meet the criteria mentioned, i.e. the TLIDIA is missing at a subsequent visit but is present at earlier visit for the same subject/record number. In the dataset below there is one record that meets that criteria (01/001 60 VISIT06 07MAR2019 2 . . Not Applicable Clear Applicable).

data WORK.WART_GRADE;
infile datalines dsd truncover;
input _usubjid:$6. visnum:32. _visname:$16. date:DATE9. _recnum:32. tlidia:32. tlithk:32. tlina:CL_NA. plares:CL_PWA. plana:CL_NA.;
format date DATE9. tlina CL_NA. plares CL_PWA. plana CL_NA.;
label _usubjid="Unique Subject Identifier" visnum="Visit Number" _visname="Visit Name" date="Date" _recnum="Wart" tlidia="Lesion diameter (mm)" tlithk="Lesion Thickness" tlina="Lesion not identified" plares="PLA Grading" plana="Lesion not identified";
datalines;
01/001 10 VISIT01 31JAN2019 1 4 1 . Thin .
01/001 10 VISIT01 31JAN2019 2 3 1 Applicable Thin Applicable
01/001 10 VISIT01 31JAN2019 3 . . Not Applicable . Not Applicable
01/001 10 VISIT01 31JAN2019 4 . . Not Applicable . Not Applicable
01/001 10 VISIT01 31JAN2019 5 . . Not Applicable . Not Applicable
01/001 10 VISIT01 31JAN2019 6 . . Not Applicable . Not Applicable
01/001 20 VISIT02 07FEB2019 1 4 1 . Thin .
01/001 20 VISIT02 07FEB2019 2 3 1 Applicable Thin Applicable
01/001 20 VISIT02 07FEB2019 3 . . Not Applicable . Not Applicable
01/001 20 VISIT02 07FEB2019 4 . . Not Applicable . Not Applicable
01/001 20 VISIT02 07FEB2019 5 . . Not Applicable . Not Applicable
01/001 20 VISIT02 07FEB2019 6 . . Not Applicable . Not Applicable
01/001 30 VISIT03 14FEB2019 1 3 . . Thin .
01/001 30 VISIT03 14FEB2019 2 3 . Applicable Thin Applicable
01/001 30 VISIT03 14FEB2019 3 . . Not Applicable . Not Applicable
01/001 30 VISIT03 14FEB2019 4 . . Not Applicable . Not Applicable
01/001 30 VISIT03 14FEB2019 5 . . Not Applicable . Not Applicable
01/001 30 VISIT03 14FEB2019 6 . . Not Applicable . Not Applicable
01/001 40 VISIT04 21FEB2019 1 3 . . Thin .
01/001 40 VISIT04 21FEB2019 2 2 . Applicable Near Clear Applicable
01/001 40 VISIT04 21FEB2019 3 . . Not Applicable . Not Applicable
01/001 40 VISIT04 21FEB2019 4 . . Not Applicable . Not Applicable
01/001 40 VISIT04 21FEB2019 5 . . Not Applicable . Not Applicable
01/001 40 VISIT04 21FEB2019 6 . . Not Applicable . Not Applicable
01/001 50 VISIT05 28FEB2019 1 3 . . Thin .
01/001 50 VISIT05 28FEB2019 2 0 . Applicable Clear Applicable
01/001 50 VISIT05 28FEB2019 3 . . Not Applicable . Not Applicable
01/001 50 VISIT05 28FEB2019 4 . . Not Applicable . Not Applicable
01/001 50 VISIT05 28FEB2019 5 . . Not Applicable . Not Applicable
01/001 50 VISIT05 28FEB2019 6 . . Not Applicable . Not Applicable
01/001 60 VISIT06 07MAR2019 1 3 . . Thin .
01/001 60 VISIT06 07MAR2019 2 . . Not Applicable Clear Applicable
01/001 60 VISIT06 07MAR2019 3 . . Not Applicable . Not Applicable
01/001 60 VISIT06 07MAR2019 4 . . Not Applicable . Not Applicable
01/001 60 VISIT06 07MAR2019 5 . . Not Applicable . Not Applicable
01/001 60 VISIT06 07MAR2019 6 . . Not Applicable . Not Applicable

[ballardw]

Please post code into a code box. The forum reformats text, removing blanks and such, and often renders data steps not readable.

To use your custom informats we need the actual Proc format code to create the informats or we can't run the code.

If you want us to use the formatted values we need the code of the formats as well.

With that in mind though this seems to get what you indicate is appropriate for Tlidia.

proc sql;
   create table wanttlidia as
   select distinct b.* 
   from (select * from WORK.WART_GRADE 
         where not missing(tlidia)) as a
         left join
         WORK.WART_GRADE as b
         on a._usubjid=b._usubjid
         and a._recnum=b._recnum
   where b.visnum > a.visnum
      and missing (b.tlidia)
   ;
quit;

[PSU_Sudzi]

Working on it now.

[PSU_Sudzi]

Below are the proc formats as well as the code from the instructions on how to post data steps here. Note that I was unable to get data with values from the data step below until I removed the infile line of code.

PROC FORMAT ;
VALUE CL_NA
0 = 'Applicable'
1 = 'Not Applicable'
;
VALUE CL_PWA
0 = 'Clear'
1 = 'Near Clear'
2 = 'Thin'
3 = 'Thick'
;

data WORK.WART_GRADE_TEST;
  infile datalines dsd truncover;
  input _usubjid:$6. visnum:32. _visname:$16. date:DATE9. _recnum:32. tlidia:32. tlithk:32. tlina:CL_NA. plares:CL_PWA. plana:CL_NA.;
  format date DATE9. tlina CL_NA. plares CL_PWA. plana CL_NA.;
  label _usubjid="Unique Subject Identifier" visnum="Visit Number" _visname="Visit Name" date="Date" _recnum="Wart" tlidia="Lesion diameter (mm)" tlithk="Lesion Thickness" tlina="Lesion not identified" plares="PLA Grading" plana="Lesion not identified";
datalines;
01/001 10 VISIT01 31JAN2019 1 4 1 . Thin .
01/001 10 VISIT01 31JAN2019 2 3 1 Applicable Thin Applicable
01/001 10 VISIT01 31JAN2019 3 . . Not Applicable . Not Applicable
01/001 10 VISIT01 31JAN2019 4 . . Not Applicable . Not Applicable
01/001 10 VISIT01 31JAN2019 5 . . Not Applicable . Not Applicable
01/001 10 VISIT01 31JAN2019 6 . . Not Applicable . Not Applicable
01/001 20 VISIT02 07FEB2019 1 4 1 . Thin .
01/001 20 VISIT02 07FEB2019 2 3 1 Applicable Thin Applicable
01/001 20 VISIT02 07FEB2019 3 . . Not Applicable . Not Applicable
01/001 20 VISIT02 07FEB2019 4 . . Not Applicable . Not Applicable
01/001 20 VISIT02 07FEB2019 5 . . Not Applicable . Not Applicable
01/001 20 VISIT02 07FEB2019 6 . . Not Applicable . Not Applicable
01/001 30 VISIT03 14FEB2019 1 3 . . Thin .
01/001 30 VISIT03 14FEB2019 2 3 . Applicable Thin Applicable
01/001 30 VISIT03 14FEB2019 3 . . Not Applicable . Not Applicable
01/001 30 VISIT03 14FEB2019 4 . . Not Applicable . Not Applicable
01/001 30 VISIT03 14FEB2019 5 . . Not Applicable . Not Applicable
01/001 30 VISIT03 14FEB2019 6 . . Not Applicable . Not Applicable
01/001 40 VISIT04 21FEB2019 1 3 . . Thin .
01/001 40 VISIT04 21FEB2019 2 2 . Applicable Near Clear Applicable
01/001 40 VISIT04 21FEB2019 3 . . Not Applicable . Not Applicable
01/001 40 VISIT04 21FEB2019 4 . . Not Applicable . Not Applicable
01/001 40 VISIT04 21FEB2019 5 . . Not Applicable . Not Applicable
01/001 40 VISIT04 21FEB2019 6 . . Not Applicable . Not Applicable
01/001 50 VISIT05 28FEB2019 1 3 . . Thin .
01/001 50 VISIT05 28FEB2019 2 0 . Applicable Clear Applicable
01/001 50 VISIT05 28FEB2019 3 . . Not Applicable . Not Applicable
01/001 50 VISIT05 28FEB2019 4 . . Not Applicable . Not Applicable
01/001 50 VISIT05 28FEB2019 5 . . Not Applicable . Not Applicable
01/001 50 VISIT05 28FEB2019 6 . . Not Applicable . Not Applicable
01/001 60 VISIT06 07MAR2019 1 3 . . Thin .
01/001 60 VISIT06 07MAR2019 2 . . Not Applicable Clear Applicable
01/001 60 VISIT06 07MAR2019 3 . . Not Applicable . Not Applicable
01/001 60 VISIT06 07MAR2019 4 . . Not Applicable . Not Applicable
01/001 60 VISIT06 07MAR2019 5 . . Not Applicable . Not Applicable
01/001 60 VISIT06 07MAR2019 6 . . Not Applicable . Not Applicable
;;;;