BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
112211
Obsidian | Level 7
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;

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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;  

 

ChrisNZ_1-1720935958612.png

 

 

 

 

View solution in original post

11 REPLIES 11
ChrisNZ
Tourmaline | Level 20

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.

 

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

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;  

 

ChrisNZ_1-1720935958612.png

 

 

 

 

110816
Calcite | Level 5
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)

mkeintz
PROC Star

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

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

--------------------------
ChrisNZ
Tourmaline | Level 20
I'd have reported the other one as spam rather than merge.
Why 2 different names?
mkeintz
PROC Star

@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

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

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

mkeintz
PROC Star

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

--------------------------

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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