Obsidian | Level 7

## Extract missing dates and additional dates

``````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"
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;

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";

drop rc i;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: Extract missing dates and additional dates

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;

%* Clean up;
drop RC I;
run;

%* Assign additional dates to correct interval;
proc sql;
from FILLDAYS
left join
and ADD1.QSDTE between FILLDAYS.QSDTE and FILLDAYS.NEXTDTE
order by FILLDAYS.PATIENT, FILLDAYS.QSDTE  ;
run;

%* Final table, concatenate additional dates ;
data WANT;
by PATIENT QSDTE;
end;
if last.QSDTE then do;
output;
end;
run;
``````

11 REPLIES 11
Tourmaline | Level 20

## Re: Extract missing dates and additional dates

I don't see any DAYn column.

Obsidian | Level 7

## Re: Extract missing dates and additional dates

Obsidian | Level 7

## Re: Extract missing dates and additional dates

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)
Tourmaline | Level 20

## Re: Extract missing dates and additional dates

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;

%* Clean up;
drop RC I;
run;

%* Assign additional dates to correct interval;
proc sql;
from FILLDAYS
left join
and ADD1.QSDTE between FILLDAYS.QSDTE and FILLDAYS.NEXTDTE
order by FILLDAYS.PATIENT, FILLDAYS.QSDTE  ;
run;

%* Final table, concatenate additional dates ;
data WANT;
by PATIENT QSDTE;
end;
if last.QSDTE then do;
output;
end;
run;
``````

Tourmaline | Level 20

## Re: Extract missing dates and additional dates

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

Calcite | Level 5

## extract additional and missing records

``````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"
above defined days (day1,day1,day2,day3)

PROC Star

## Re: Extract missing dates and additional dates

@ChrisNZ wrote:

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

That post has now been merged with this one.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tourmaline | Level 20

## Re: Extract missing dates and additional dates

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

## Re: Extract missing dates and additional dates

@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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tourmaline | Level 20

## Re: Extract missing dates and additional dates

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

PROC Star

## Re: Extract missing dates and additional dates

You're right.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Discussion stats
• 11 replies
• 634 views
• 2 likes
• 4 in conversation