Creating code to apply a list of changes to a dataset

[sasuser31]

I am managing a data set from surveys that are administered through a program. Sometimes, an error occurs when entering an answer or variable during the survey and something needs to be changed after the survey data has already been stored. These changes are stored in a database given the following information:

Process Order (p_order): Order in which the error should be processed (This variable is created in case there are multiple errors on one survey)

Survey ID (surid): ID of the survey participant

Interviewer ID (icode): ID of the interviewer

Interview Start time (start): Start time of the interview

Interview Date (idate): Date of the interview

Variable Name (name): Name of the variable that needs to be changed

Old Value (o_value): Old value of the variable that needs to be changed

New Value (n_value): New value of the variable that needs to be changed

I need to create a SAS code that I can run which will search through the survey SAS data set and find the matching observation with the same surid, icode, start, and idate and also has the same o_value for the desired variable we want to change. Then the code should change the value from old to new.

I know i could do it by hand, but I am hoping to find a way to efficiently code this up to automate it. I think it would make sense to pull one observation at a time from the data error log and apply it to the survey data.

Hoping for any advice you can provide!!

[PGStats]

What happens if you don't find the old value?

If you made changes irrespective of the old value, this operation could be done simply with an UPDATE statement in a datastep.

[sasuser31]

How would I use an update statement? I cant seem to put the pieces together since there are several different variables that  could need to  be updated.

[ballardw]

---

@sasuser31 wrote:

I am managing a data set from surveys that are administered through a program. Sometimes, an error occurs when entering an answer or variable during the survey and something needs to be changed after the survey data has already been stored. These changes are stored in a database given the following information:

Process Order (p_order): Order in which the error should be processed (This variable is created in case there are multiple errors on one survey)

Survey ID (surid): ID of the survey participant

Interviewer ID (icode): ID of the interviewer

Interview Start time (start): Start time of the interview

Interview Date (idate): Date of the interview

Variable Name (name): Name of the variable that needs to be changed

Old Value (o_value): Old value of the variable that needs to be changed

New Value (n_value): New value of the variable that needs to be changed

I need to create a SAS code that I can run which will search through the survey SAS data set and find the matching observation with the same surid, icode, start, and idate and also has the same o_value for the desired variable we want to change. Then the code should change the value from old to new.

I know i could do it by hand, but I am hoping to find a way to efficiently code this up to automate it. I think it would make sense to pull one observation at a time from the data error log and apply it to the survey data.

Hoping for any advice you can provide!!

---

Provide some example data of your correction set and base data in the form of a data step.

You are actually missing a piece of information that will be critical for any automated process to work: Is the value of "Old Value" and/or "New Value" numeric or character? So there will likely have to be a step to pull that information from the data to be corrected so the values of o_value and n_value can be presented correctly. Also Formats may come into play.

I am assuming that the data types of your other variables exactly match the values in you data to correct.

[sasuser31]

So here is a small made up example (cannot provide real data)

Survey Data

surid	icode	Idate	start	eligibility	Venue	Age	Sex
100	3	12/20/2018	9:38:24	1	2	59	1
150	3	12/20/2018	10:58:27	1	2	46	2
200	1	12/20/2018	12:19:20	1	2	35	1
250	1	12/20/2018	3:52:10	0	2	18	2
300	2	12/25/2018	12:35:26	1	2	33	1
350	6	12/25/2018	13:07:36	1	35	51	2
400	8	12/25/2018	13:24:59	0	35	25	1
450	2	12/25/2018	16:07:42	1	35	62	2
500	6	12/25/2018	18:24:19	1	35	43	1

Error Log:

P_order	surid	icode	idate	start	Name	o_value	n_value
1	200	01	12/20/2018	12:19:20	surid	200	300
2	200	01	12/20/2018	12:19:20	eligibility	1	0
1	300	02	12/25/2018	12:35:26	Venue	02	35
1	400	08	12/25/2018	13:24:59	Age	25	52
1	500	06	12/252018	18:24:19	Sex	1	2

I hope this helps!