topic Creating code to apply a list of changes to a dataset in SAS Programming

Creating code to apply a list of changes to a dataset

sasuser31 — Tue, 19 Jun 2018 17:23:26 GMT

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

Re: Creating code to apply a list of changes to a dataset

PGStats — Tue, 19 Jun 2018 18:02:59 GMT

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.

Re: Creating code to apply a list of changes to a dataset

ballardw — Tue, 19 Jun 2018 18:21:23 GMT

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

Re: Creating code to apply a list of changes to a dataset

sasuser31 — Tue, 19 Jun 2018 21:08:05 GMT

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!

Re: Creating code to apply a list of changes to a dataset

sasuser31 — Thu, 21 Jun 2018 21:49:37 GMT

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.