topic Re: Extract missing dates and additional dates in SAS Programming

Extract missing dates and additional dates

112211 — Wed, 10 Jul 2024 06:38:31 GMT

Data sv ;
 input cpevent$1-8 svstdt $9-18 ;
  patient = "1015";
  svstdt1 = input(svstdt,date9.);
 cards ;
week0   20apr2022
week4   18may2022
week8   20jun2022
week26  19oct2022
week30  22nov2022
week34  14dec2022
week52  18apr2023
week56  17may2023
week60  19jun2023
week78  17oct2023
week82  15nov2023
week86  13dec2023
week104 17apr2024
;

Data qs ;
 patient = "1015";
 do qsdat = '22apr2022'd to '25apr2022'd,'18may2022'd to '20may2022'd,
 '21jun2022'd,'19oct2022'd to '22oct2022'd,'14dec2022'd to '17dec2022'd,
 '18apr2023'd to '21apr2023'd ,'17may2023'd to '19may2023'd,'19jun2023'd to '21jun2023'd,
 '19oct2023'd to '22oct2023'd,'15nov2023'd to '18nov2023'd,
 '13dec2023'd to '16dec2023'd,'18apr2024'd ;
 output;
 end ;
 format qsdat date9.;
run;

I need help in programming based on below conditions :

Created above data based on my real time study, And Requiredoutput dataset also below attached.

(This is emergency requirement for my study)
for the svstdt check the below logics :
1)for day0 check if qsdat = svstdt ,if yes populate the qsdat inday0 column.
keep lt blank
2)for day1 check if qsdat = svstdt+1 ,if yes populate the qsdat in day1 column.
keep lt blank
3)for day2 check if qsdat = svstdt+2 ,if yes populate the qsdat in day2 column.
keep lt blank
4)for day3 check if qsdat = svstdt+3 ,if yes populate the qsdat in day3 column.
keep lt blank
if any columns from day0 ,day1,day2,day3 is blank
then populate flag as "missing"
if additional records were received in qsdat which is outside of
above defined days (day1,day1,day2,day3)

I am tried below programme everything okay but not getting additional records :

Please any one correct it .

data sv_qs;
if _n_ = 1 then do;
declare hash h(dataset:'qs');
h.defineKey('patient', 'qsdat');
h.defineData('qsdat');
h.defineDone();
end;

set sv;

array days[*] day0-day3 additional ;
do i = 0 to 4;
rc = h.find(key:patient, key:svstdt1+i);
if rc = 0 then days[i+1] = svstdt1+i;
/* else days[i+1] =.;*/
end;

rc = h.find(key:patient, key:qsdat);
if rc = 0 and qsdat < svstdt1 or qsdat > svstdt1 + 3 then flag = "extra";

format qsdat additional additional1 day0 day1 day2 day3 date9. ;
drop rc i;
run;

extract additional and missing records

110816 — Wed, 10 Jul 2024 03:57:51 GMT

Data sv ;
 input cpevent$1-8 svstdt $9-18 ;
  patient = "1015";
 cards ;
week0   20apr2022
week4   18may2022
week8   20jun2022
week26  19oct2022
week30  22nov2022
week34  14dec2022
week52  18apr2023
week56  17may2023
week60  19jun2023
week78  17oct2023
week82  15nov2023
week86  13dec2023
week104 17apr2024
;

Data qs ;
 patient = "1015";
 do qsdat = '22apr2022'd to '25apr2022'd,'18may2022'd to '20may2022'd,
 '21jun2022'd,'19oct2022'd to '22oct2022'd,'14dec2022'd to '17dec2022'd,
 '18apr2023'd to '21apr2023'd ,'17may2023'd to '19may2023'd,'19jun2023'd to '21jun2023'd,
 '19oct2023'd to '22oct2023'd,'15nov2023'd to '18nov2023'd,
 '13dec2023'd to '16dec2023'd,'18apr2024'd ;
 output;
 end ;
 format qsdat date9.;
run;

I need help in programming for below condition. Iam created above data based on my real time project. Required output dataset also attached. Output same as attached Dataset below.

for the svstdt check the below logics :
1)for day0 check if qsdat = svstdt ,if yes populate the qsdat inday0 column.
keep lt blank
2)for day1 check if qsdat = svstdt+1 ,if yes populate the qsdat in day1 column.
keep lt blank
3)for day2 check if qsdat = svstdt+2 ,if yes populate the qsdat in day2 column.
keep lt blank
4)for day3 check if qsdat = svstdt+3 ,if yes populate the qsdat in day3 column.
keep lt blank
if any columns from day0 ,day1,day2,day3 is blank
then populate flag as "missing"
if additional records were received in qsdat which is outside of
above defined days (day1,day1,day2,day3)

Re: Extract missing dates and additional dates

ChrisNZ — Sat, 13 Jul 2024 06:36:54 GMT

I don't see any DAYn column.

Please post the expected result as a table as some of us can't/won't download files.

Re: Extract missing dates and additional dates

112211 — Sat, 13 Jul 2024 07:14:31 GMT

Re: Extract missing dates and additional dates

112211 — Sat, 13 Jul 2024 07:18:22 GMT

Based on the conditions mentioned above and two datasets, I need output like the attached image.
I also pasted my program also (My sample programme)

Re: Extract missing dates and additional dates

ChrisNZ — Sun, 14 Jul 2024 05:47:06 GMT

You could do everything in one data step, but it's easier to code and follow the logic if you save the additional dates in a subsequent step.

data SV ;
  retain PATIENT '1015';
  input CPEVENT :$8. SVSTDTE :date9. ;
  format SVSTDTE date9.;
cards ;
week0   20apr2022
week4   18may2022
week8   20jun2022
week26  19oct2022
week30  22nov2022
week34  14dec2022
week52  18apr2023
week56  17may2023
week60  19jun2023
week78  17oct2023
week82  15nov2023
week86  13dec2023
week104 17apr2024
;

data QS;
  retain PATIENT '1015';
  do QSDTE = '22apr2022'd to '25apr2022'd, '18may2022'd to '20may2022'd, '21jun2022'd,
             '19oct2022'd to '22oct2022'd, '14dec2022'd to '17dec2022'd,
             '18apr2023'd to '21apr2023'd, '17may2023'd to '19may2023'd, 
             '19jun2023'd to '21jun2023'd, '19oct2023'd to '22oct2023'd,
             '15nov2023'd to '18nov2023'd, '13dec2023'd to '16dec2023'd, '18apr2024'd ;
    output;
  end;
  format QSDTE date9.;
run;

data FILLDAYS;

  %* Define lookup table for QS date;
  if _n_ = 1 then do;
    if 0 then set QS;
    declare hash H(dataset:'QS', ordered: 'y');
    h.defineKey( 'PATIENT', 'QSDTE');
    h.defineData('PATIENT', 'QSDTE');
    h.defineDone();
  end;

  %* Read SV date and next SV date ;
  set SV nobs=NOBS;
  if _N_ ne NOBS then set SV(keep=SVSTDTE rename=(SVSTDTE=NEXTDTE) firstobs=2);
  else NEXTDTE='31DEC2999'd;
  
  %* Declare DAYn variables;
  array DAYS[*] DAY0-DAY3 ;
  format DAY0-DAY3 date9. ;
  
  %* Populate DAYn variables;
  do I = 0 to 3;
    RC = H.find(key:PATIENT, key:SVSTDTE+I);    
    if RC = 0 then do;
      DAYS[I+1] = SVSTDTE+I;
      H.remove(key:PATIENT, key:SVSTDTE+I);  
    end;  
  end;
  
  %* Set missing flag;
  if nmiss(of DAY:)=4 then FLAG = 'missing';
  
  %* Export unused QS dates;
  if _N_=NOBS then H.output(dataset:'ADD1'); 
  
  %* Clean up;
  drop RC I;
run;

%* Assign additional dates to correct interval;
proc sql;
  create table ADD2 as
  select FILLDAYS.*, ADD1.QSDTE as ADDDTE
  from FILLDAYS
         left join
       ADD1
         on  FILLDAYS.PATIENT = ADD1.PATIENT 
         and ADD1.QSDTE between FILLDAYS.QSDTE and FILLDAYS.NEXTDTE
  order by FILLDAYS.PATIENT, FILLDAYS.QSDTE  ;
run;  

%* Final table, concatenate additional dates ;
data WANT;
  set ADD2;  
  length ADDITIONAL $80 ;
  retain ADDITIONAL;
  by PATIENT QSDTE;
  if ADDDTE then do;
    if first.QSDTE then ADDITIONAL=put(ADDDTE,date9.);
    else ADDITIONAL=catx('.', ADDITIONAL, put(ADDDTE,date9.));
  end;
  if last.QSDTE then do;
    output; 
    ADDITIONAL=' ';
  end;
  keep PATIENT CPEVENT DAY: ADDITIONAL;
run;

Re: Extract missing dates and additional dates

ChrisNZ — Sun, 14 Jul 2024 05:51:05 GMT

Why do you post exactly the same question under a different name?

https://communities.sas.com/t5/SAS-Programming/extract-additional-and-missing-records/m-p/935259

Re: Extract missing dates and additional dates

mkeintz — Sun, 14 Jul 2024 12:31:06 GMT

@ChrisNZ wrote:

Why do you post exactly the same question under a different name?

https://communities.sas.com/t5/SAS-Programming/extract-additional-and-missing-records/m-p/935259

That post has now been merged with this one.

Re: Extract missing dates and additional dates

ChrisNZ — Sun, 14 Jul 2024 13:27:18 GMT

I'd have reported the other one as spam rather than merge.
Why 2 different names?

Re: Extract missing dates and additional dates

mkeintz — Sun, 14 Jul 2024 15:34:49 GMT

@ChrisNZ wrote:
I'd have reported the other one as spam rather than merge.
Why 2 different names?

If there are two different names for this wholly identical post, then it may be that these are classmates asking about a homework task. If so, I think there is a benefit to merging.

Re: Extract missing dates and additional dates

ChrisNZ — Mon, 15 Jul 2024 05:39:41 GMT

The posts are exactly the same, same formatting, same attachment. And the user names too: six digits. Quite unusual.

Re: Extract missing dates and additional dates

mkeintz — Wed, 17 Jul 2024 20:14:08 GMT

You're right.