topic Re: fetch each subjects corresponding records from multiple datasets in a library in SAS Programming

fetch each subjects corresponding records from multiple datasets in a library

SAS-PD — Sat, 01 Jun 2024 03:07:45 GMT

Hello, I would like to fetch each subjects corresponding records from multiple datasets in a library where date criteria fails.

Example
Dataset A
Subjid. Start date
Subj1 01/01/2001
Subj2. 02/02/2002
Subj3. 03/03/2003
Subj4. 04/04/2004

Dataset B
Subjid. End date

Subj1 01/01/2021
Subj2. 02/02/2022
Subj3. 03/03/2023
Subj4. 04/04/2024

Dataset C
Subjid Lab date
Subj1 01/01/1999
Subj2. 02/02/2001
Subj3. 03/03/2001
Subj4. 04/04/2025

Dataset D
Subjid Test date. Edit date
Subj1 01/01/2011 01/01/2020
Subj2. 02/02/2011 02/02/2023
Subj3. 03/03/2025 03/03/2013
Subj4. 04/04/2014 04/04/2014

From above example report should have below that violated date rule -

Dataset C

Subjid Lab date
Subj1 01/01/1999
Subj4. 04/04/2025

Dataset D
Subjid Test date. Edit date
Subj2. 02/02/2023
Subj3. 03/03/2025

I would like to fetch subjects from dataset C and D where subjects with date(s) before dataset A start date and after dataset B end date. I have about 52 datasets with dates to be checked , most has different date variable

Kindly help!

Re: fetch each subjects corresponding records from multiple datasets in a library

Mazi — Tue, 04 Jun 2024 11:01:24 GMT

data A;
infile datalines;
input Subjid $ Start_date ddmmyy10.;
format start_date e8601da.;
datalines;
Subj1 01/01/2001
Subj2 02/02/2002
Subj3 03/03/2003
Subj4 04/04/2004
;
run;

data B;
infile datalines;
input Subjid $ end_date ddmmyy10.;
format end_date e8601da.;
datalines;
Subj1 01/01/2021
Subj2 02/02/2022
Subj3 03/03/2023
Subj4 04/04/2024
;
run;

Data C;
infile datalines;
input Subjid $ lab_date ddmmyy10.;
format lab_date e8601da.;
datalines;
Subj1 01/01/1999
Subj2 02/02/2001
Subj3 03/03/2001
Subj4 04/04/2025
;
run;

data D;
infile datalines;
input Subjid $ Test_date ddmmyy10.;
format test_date e8601da.;
datalines;
Subj1 01/01/2011
Subj2 02/02/2011
Subj3 03/03/2025
Subj4 04/04/2014
;
run;

proc sql;
	create table a_b as 
	select a.*
		  ,b.end_date
	from a natural join b;
quit;

options mprint;
%macro checkForViolation(datasets=C#D,dates=lab_date#test_date);
	%local i;

	%do i=1 %to %sysfunc(countw(&datasets, #));
		proc sql;
			create table %scan(&datasets, &i, #)_violate as
				select l.* 
				from %scan(&datasets, &i, #) as l left join a_b as r
				on l.subjid=r.subjid
				where l.%scan(&dates, &i, #) < r.start_date or l.%scan(&dates, &i, #) > r.end_date;
		quit;
	%end;
%mend checkForViolation;


%checkForViolation;

Is this what you mean

Re: fetch each subjects corresponding records from multiple datasets in a library

yabwon — Tue, 04 Jun 2024 12:12:47 GMT

You wrote: " I have about 52 datasets with dates to be checked , most has different date variable"

Do you have a list of those files? or maybe they are located in a single directory/libname?

Do you have any rule for recognising that date variables? e.g., the end with "...DT" or "...date" ? Or maybe you have a list of those variables?

Bart

Re: fetch each subjects corresponding records from multiple datasets in a library

yabwon — Tue, 04 Jun 2024 12:14:40 GMT

Are you sure you want it to be:

"subjects with date(s) before dataset A start date and after dataset B end date."

I think it should be:

"subjects with date(s) before dataset A start date OR after dataset B end date."

Bart

Re: fetch each subjects corresponding records from multiple datasets in a library

SAS-PD — Thu, 06 Jun 2024 15:13:55 GMT

All datasets are in the same directory/libname, each dataset have different date variable that I need to check against start date and end date.

I am trying to find out all the subjects /records where the date entered is before start date OR after end date. Start date and end date comes from 2 different datasets and those 2 dates are checked with all dates entered in other 52 datasets in the same directory/libname.

Re: fetch each subjects corresponding records from multiple datasets in a library

Mazi — Fri, 07 Jun 2024 06:14:27 GMT

Hi @SAS-PD , Did you try the solution I gave you above? Also, if all these datasets are in the same library, does that mean you have 54 datasets? 2 for the start and end dates and 52 to check against. Lastly as mentioned by @yabwon do the date variables have common prefix or suffixes we can use to write logic to easily select them dynamically?

Re: fetch each subjects corresponding records from multiple datasets in a library

Kurt_Bremser — Fri, 07 Jun 2024 07:34:26 GMT

Is subjid unique in all datasets?

What are the names for the date variables in all the other datasets?

Is subjid character or numeric?

Not that the last question would be unnecessary if you had posted your example data in the proper manner - working DATA steps with DATALINES.

Re: fetch each subjects corresponding records from multiple datasets in a library

SAS-PD — Fri, 07 Jun 2024 15:34:56 GMT

Hi Mazi, thank you for your response! above code did work and fetched records that did not fall between start and end date. 🙂 There are total of 52 datasets in the library including 2 with start and end date. There is no common prefix or suffix for a date variable, I need to check all the dates that are present in each dataset. For data cleaning I am trying to check if there are any records that has date not between start and end date in the same library.

Re: fetch each subjects corresponding records from multiple datasets in a library

SAS-PD — Fri, 07 Jun 2024 15:39:37 GMT

Hi Kurt, thank you for your response!

Is subjid unique in all datasets? -->yes

What are the names for the date variables in all the other datasets? -->does not have any common prefix or suffix

Is subjid character or numeric? --> character

Sorry about not posting working DATA steps with DATALINES...this is my first post and will keep this in mind moving forward.

Re: fetch each subjects corresponding records from multiple datasets in a library

Kurt_Bremser — Fri, 07 Jun 2024 16:01:40 GMT

So you can first merge all datasets by subjid. Then, retrieve the names of all numeric variables in this new dataset which are not your start and end dates (DICTIONARY.COLUMNS) into a macro variable. Use this macro variable to create an array, so you can loop through it and flag unwanted values.

Re: fetch each subjects corresponding records from multiple datasets in a library

mkeintz — Fri, 07 Jun 2024 22:47:09 GMT

52 files? Really? With (up to) 52 different date variable names? You should probably merge all the datasets by subjid, which would make a program data vector with START_DATE, END_DATE and 52 other date varibles.

You don't need to run a macro 52 times, but you can benefit from making 2 macrovars, each a list of 52 names, as I do with only 2 names below.

data A;
infile datalines;
input Subjid $ Start_date ddmmyy10.;
format start_date e8601da.;
datalines;
Subj1 01/01/2001
Subj2 02/02/2002
Subj3 03/03/2003
Subj4 04/04/2004
;
run;

data B;
infile datalines;
input Subjid $ end_date ddmmyy10.;
format end_date e8601da.;
datalines;
Subj1 01/01/2021
Subj2 02/02/2022
Subj3 03/03/2023
Subj4 04/04/2024
;
run;

Data C;
infile datalines;
input Subjid $ lab_date ddmmyy10.;
format lab_date e8601da.;
datalines;
Subj1 01/01/1999
Subj2 02/02/2001
Subj3 03/03/2001
Subj4 04/04/2025
;
run;

data D;
infile datalines;
input Subjid $ Test_date ddmmyy10.;
format test_date e8601da.;
datalines;
Subj1 01/01/2011
Subj2 02/02/2011
Subj3 03/03/2025
Subj4 04/04/2014
;
run;


%let dslist = c             d         ;
%let varlst = lab_date      test_date ;

data _null_;
  merge a b &dslist  end=end_of_merge;
  by subjid;

  array dates {*}  &varlst;

  if _n_=1 then do;
    declare hash h ;   
    declare hash hoh ();
      hoh.definekey('i');
      hoh.definedata('h');
      hoh.definedone();
    do i=1 to dim(dates);
      h=_new_ hash(ordered:'a');
      h.definekey('subjid');
      h.definedata('subjid','start_date','end_date',vname(dates{i}));
      h.definedone();
      hoh.add();
    end;
  end;
  do i=1 to dim(dates);
    if dates{i}^=.  and (dates{i}<start_date or dates{i}>end_date) then do;
      hoh.find();
      h.add();
    end;
  end;
  if end_of_merge then do i=1 to dim(dates);
    hoh.find();
    if h.num_items>0 then h.output(dataset:cats('exclude_',scan("&dslist",i)));
  end;
run;

To expand from 2 datasets (C and D) to 52, just expand the two macrovars DSLIST and VARLST. None of the remaining code has to be changed.

BTW, instead of 52 datasets, I think you would be far better off with 1 dataset, containing 52 dummy variables indicating whether the corresponding data variable is out of range. Then you could just do filters on those dummy variables (or any combination of those dummy variables)

Re: fetch each subjects corresponding records from multiple datasets in a library

Tom — Sat, 08 Jun 2024 00:28:15 GMT

First question is do you really have non-standard variable names like that? With spaces in the name? That will make the coding harder. You will have to use name literals to reference the variables.

You can use the FMTINFO() function to find all of the variables that have a DATE type format attached to them (DATE, YYMMDD, etc.). So if all of the datasets are in the library pointed to by the libref MYLIB the code would look like this.

proc contents data=MYLIB._all_ noprint out=contents; run;
data datevars;
  set contents;
  where 'date'=fmtinfo(format,'cat');
  keep libname memname name;
run;

Now you can use this list to generate the code you want. Do you want to process each dataset separately? Let's assume you already have made the dataset with the three key variables (let's call them SUBJECT, START and END). Let's call this dataset SUBJECTS. Now for each dataset that has date variables you just want to do something like:

data bad_ds1 ;
  merge subjects(in=in1) mylib.ds1(in=in2);
  by subject;
  if in2;
  array dates datevar1 datevar2 ;
  anybad=0;
  do i=1 to dim(dates) until (anybad);
    anybad = (.Z < dates[i] < START) or (dates[i] > END);
  end;
  if anybad;
run;

Which will make a subset of the data with just the observations where at least one of the DATE variables is populated but not in the range from START to END (where START and END are for that particular subject). Note this assumes that all of the datasets are already sorted by the SUBJECT variable.

So the only thing that changes in that as you work you way through the list of data variables is dataset names and the list of variables in the ARRAY statement. So you could easily generate it from the DATEVARS dataset.

filename code temp;
data _null_;
  file code;
  set datevars;
  by memname ;
  if first.memname then put 
  'data bad_' memname ';'
/ '  merge subjects(in=in1) mylib.' memname '(in=in2);'
/ '  by subject;'
/ '  if in2;'
/ '  array dates ' @
  ;
  put name @;
  if last.memname then put 
  ';'
/ '  anybad=0;'
/ '  do i=1 to dim(dates) until (anybad);'
/ '    anybad = (.Z < dates[i] < START) or (dates[i] > END);'
/ '  end;'
/ '  if anybad;'
/ 'run;'
  ;
run;

Re: fetch each subjects corresponding records from multiple datasets in a library

Mazi — Mon, 10 Jun 2024 09:20:39 GMT

proc datasets lib=work kill mt=data nolist nodetails nowarn;
quit;

data A;
infile datalines;
input Subjid $ Start_date ddmmyy10.;
format start_date e8601da.;
datalines;
Subj1 01/01/2001
Subj2 02/02/2002
Subj3 03/03/2003
Subj4 04/04/2004
;
run;

data B;
infile datalines;
input Subjid $ end_date ddmmyy10.;
format end_date e8601da.;
datalines;
Subj1 01/01/2021
Subj2 02/02/2022
Subj3 03/03/2023
Subj4 04/04/2024
;
run;

Data C;
infile datalines;
input Subjid $ lab_date ddmmyy10.;
format lab_date e8601da.;
datalines;
Subj1 01/01/1999
Subj2 02/02/2001
Subj3 03/03/2001
Subj4 04/04/2025
;
run;

data D;
infile datalines ;
input Subjid $ Test_date ddmmyy10.;
format test_date edit_date e8601da.;
edit_date = test_date;
datalines;
Subj1 01-01-2011
Subj2 01-02-2011
Subj3 03-03-2025
Subj4 04-04-2014
;
run;

data config;
	length data varname $32;
	infile datalines dlm=',' missover;
	input data $ varname $;
/*Add all datasets and each variable to check against in the config dataset*/
	datalines;
C,lab_date
D,test_date
D,edit_date
;
run;

options mprint symbolgen;
%macro check_for_violation;
	%local i obs operator j;
	proc sql;
		create table all_data as
			select memname from dictionary.tables 
		where libname='WORK' and memname not in ("A" "B" "CONFIG" "ALL_DATA");
		%let obs = &sqlObs;
		%do i=1 %to &sqlObs;
			%local ds&i;
		%end;
		select memname into: ds1- from all_data;
	quit;

	data _null_;
		set config;
		by data notsorted;
		if first.data then do;
			size=0;
			varcnt=0;
		end;
		size+1;
		varcnt+1;
		call symputx(catx('_', data, varcnt), varname,'l');
		if last.data then call symputx(catx('_', data, 'size'), varcnt,'l');
	run;
	
	%let operator=;
	proc sql;
		%do i=1 %to &obs;
			create table &&ds&i.._violate as
				select l.* 
				from &&ds&i as l left join 
					 a      as r 
				on l.subjid=r.subjid left join b as m
				on r.subjid=m.subjid
				where 
				%do j=1 %to %unquote(%nrstr(&)&&ds&i.._size);
					&operator
					(. < l.%unquote(%nrstr(&)&&ds&i.._&j) < r.start_date) or (l.%unquote(%nrstr(&)&&ds&i.._&j) > m.end_date > .)
					%let operator=or;
				%end;
				;
			%let operator=;	
		%end;
	quit;
%mend check_for_violation;


%check_for_violation;

Hi @SAS-PD , I updated the code to use a config dataset to make it easier to specify datasets and their variables. All you would need to do is add each of your datasets to the config and the variables related to that dataset that you want checked. Since your variables do not follow any convention as you said earlier, this is the best I could come up with. I hope it helps.

Re: fetch each subjects corresponding records from multiple datasets in a library

SAS-PD — Mon, 10 Jun 2024 19:04:52 GMT

Thank you for your response!

The actual library has more than 52 datasets and about 115 dates variables to be checked against start and end dates. I agree with you to create 1 dataset with all 115+ dates variables from 52+ datasets (all dates variable in a librabry), subjid being the common variable among all datasets in the library. I was able to create a work dataset that has all datasets with only date variables in library using

proc contents data = d_test._all_ out=content; run;

data content1;
set content;
where format = 'MMDDYY' ;
keep libname memname name n;
n+1;
run;

not sure how to proceed from here...kindly help!

Re: fetch each subjects corresponding records from multiple datasets in a library

SAS-PD — Mon, 10 Jun 2024 19:16:17 GMT

Hello, thank you for your response!

I apologize as I am new to SAS... How do I sort all datasets in a library to subjid?

Re: fetch each subjects corresponding records from multiple datasets in a library

SAS-PD — Mon, 10 Jun 2024 19:28:52 GMT

I created a work dataset with all datasets with all date variables in library using Proc Content not sure how to proceed from here...kindly help!

data All_Dates;
infile datalines;
input libname $ memname $ name $ n;
datalines;
D_TEST C lab_date 1
D_TEST D Test_date 2
D_TEST E n_AEENDAT 3
D_TEST E n_AESITEAW 4
D_TEST F n_AESTDAT 5
D_TEST G VISIT_DATE 6
D_TEST G n_FTDAT 7
D_TEST G VISIT_DATE 8
;
run;

I need to check all dates to see if they are between D_TEST.A.STart_date and D_TEST.B.End_date

Re: fetch each subjects corresponding records from multiple datasets in a library

SAS-PD — Mon, 10 Jun 2024 19:35:28 GMT

Is there a way to use dataset name and date variable from

proc contents data = d_test._all_ out=content; run;

data content1;
set content;
where format = 'MMDDYY' and name ^= 'EDIT_DATE';
keep libname memname name label n;
n+1;
run;

Re: fetch each subjects corresponding records from multiple datasets in a library

Kurt_Bremser — Tue, 11 Jun 2024 07:57:25 GMT

@SAS-PD wrote:

Hello, thank you for your response!

I apologize as I am new to SAS... How do I sort all datasets in a library to subjid?

Given the dataset you already have, like this:

data _null_;
set all_dates;
call execute("
  proc sort data="
!! catx(".",libname,memname) !!
  ";
  by subjid;
  run;
");
run;

Re: fetch each subjects corresponding records from multiple datasets in a library

Mazi — Tue, 11 Jun 2024 08:11:19 GMT

Yes, We absolutely can. instead of manually adding updating the config dataset, you can use this instead. Let me know if you still need it.

Re: fetch each subjects corresponding records from multiple datasets in a library

Kurt_Bremser — Tue, 11 Jun 2024 09:24:13 GMT

Another method to create code from data, by using a temporary file:

filename incfile temp;

data _null_;
length line $200;
file incfile;
set all_datasets;
by libname memname;
if _n_ = 1
then do;
  line = "data " !! catx(".",libname,"all_combined") !! "; merge a b";
  put line;
end;
if first.memname
then do;
  line = catx(".",libname,memname) !! "(keep=subjid ";
  put line;
end;
put name;
if last.memname then put ")";
if done then put "; by subjid; run;";
run;

data _null_;
infile incfile truncover;
input line $200.;
put line;
run;
/* this step is for control; if the code in the log is OK, submit the following include */

%include incfile;

filename incfile clear;

With the combined dataset, you can define an array over the date variables and run a check in a loop.