Hello, This is my first post here, so I'll try to be as clear as possible (I'm currently using SAS OnDemand for Academics). I have to generate a listing of queries monthly, and use the previous generated queries sas file (if it exists) to update the new one and avoid sending a query which has already been answered. What I want to update in the sas file would be the date and the status of the newly generated queries. Here is a short example of the sas file structure, and how it should looks like at the end : This is the first iteration, the generated table always has the Ongoing status. data table1; infile cards dsd dlm=',' truncover ; length record_id $7 Center $2 Event_name $20 Repeat_instance Form $20 Query $50 Date_creation Editcheck_ID $20 Status $50; input record_id $ Center $ Event_name $ Repeat_instance Form $ Query $ Date_creation :yymmdd10. Editcheck_ID $ Status $; cards; 01-001, 01, Procedure, ., Procedure, Incorrect date, 2024-08-06, procdt_A, Ongoing 01-001, 01, Procedure, ., Treatment, Missing data, 2024-08-06, trtname_A, Ongoing 01-001, 01, Procedure, 1, AE, Discrepancy in AE date, 2024-08-06, aedt_A, Ongoing 02-015, 02, Visit 1, ., Treatment, Missing data, 2024-08-06, trtname_A, Ongoing 02-015, 02, Visit 1, 3, AE, AE is not declared, 2024-08-06, aename_A, Ongoing 02-003, 02, Procedure, ., Treatment, Out of bounds data, 2024-08-06, trtdose_A, Ongoing ; run; I send the previous table in a Excel file to each center, and I receive it with the status updated a month later. data table1_returned; infile cards dsd dlm=',' truncover ; length record_id $7 Center $2 Event_name $20 Repeat_instance Form $20 Query $50 Date_creation Editcheck_ID $20 Status $50; input record_id $ Center $ Event_name $ Repeat_instance Form $ Query $ Date_creation :yymmdd10. Editcheck_ID $ Status $; format Date_creation yymmdd10.; cards; 01-001, 01, Procedure, ., Procedure, Incorrect date, 2024-08-06, procdt_A, Ongoing 01-001, 01, Procedure, ., Treatment, Missing data, 2024-08-06, trtname_A, Closed - value corrected 01-001, 01, Procedure, 1, AE, Discrepancy in AE date, 2024-08-06, aedt_A, Closed - value corrected 02-015, 02, Visit 1, ., Treatment, Missing data, 2024-08-06, trtname_A, Ongoing 02-015, 02, Visit 1, 3, AE, AE is not declared, 2024-08-06, aename_A, Closed - value corrected 02-003, 02, Procedure, ., Treatment, Out of bounds data, 2024-08-06, trtdose_A, Closed - value not corrected ; run; This is the second iteration, some older queries appear again. data table2; infile cards dsd dlm=',' truncover ; length record_id $7 Center $2 Event_name $20 Repeat_instance Form $20 Query $50 Date_creation Editcheck_ID $20 Status $50; input record_id $ Center $ Event_name $ Repeat_instance Form $ Query $ Date_creation :yymmdd10. Editcheck_ID $ Status $; cards; 05-002, 05, Inclusion, ., Inclusion, Incorrect date, 2024-09-13, incdt_A, Ongoing 01-001, 01, Procedure, ., Procedure, Incorrect date, 2024-09-13, procdt_A, Ongoing 05-002, 05, Inclusion, ., Medical history, Out of bounds data, 2024-09-13, mhiketdose_A, Ongoing 06-007, 06, Inclusion, ., Demography, Discrepancy in Birth date, 2024-09-13, dembddt_A, Ongoing 07-015, 07, Visit 3, ., Treatment, Missing data, 2024-09-13, trtname_A, Ongoing 02-015, 02, Visit 1, ., Treatment, Missing data, 2024-09-13, trtname_A, Ongoing 02-003, 02, Procedure, ., Treatment, Out of bounds data, 2024-09-13, trtdose_A, Ongoing ; run; And this is where I'm stuck, on how to update the new dataset by comparing it to the previous one. Since each real dataset can be a thousand lines long, I don't think a proc compare is the best. If the queries already exists and has been answered (ex : out of bounds data) it should be deleted, if it exists and has not been answered, the Date_creation should be updated. data table2_final; infile cards dsd dlm=',' truncover ; length record_id $7 Center $2 Event_name $20 Repeat_instance Form $20 Query $50 Date_creation Editcheck_ID $20 Status $50; input record_id $ Center $ Event_name $ Repeat_instance Form $ Query $ Date_creation :yymmdd10. Editcheck_ID $ Status $; cards; 05-002, 05, Inclusion, ., Inclusion, Incorrect date, 2024-09-13, incdt_A, Ongoing 01-001, 01, Procedure, ., Procedure, Incorrect date, 2024-08-06, procdt_A, Ongoing 05-002, 05, Inclusion, ., Medical history, Out of bounds data, 2024-09-13, mhiketdose_A, Ongoing 06-007, 06, Inclusion, ., Demography, Discrepancy in Inclusion date, 2024-09-13, dembddt_A, Ongoing 07-015, 07, Visit 3, ., Treatment, Missing data, 2024-09-13, trtname_A, Ongoing 02-015, 02, Visit 1, ., Treatment, Missing data, 2024-08-06, trtname_A, Ongoing ; run; Thank you for your answers !
... View more
