DATA Step, Macro, Functions and more

Creating code to apply a list of changes to a dataset

Reply
Contributor
Posts: 22

Creating code to apply a list of changes to a dataset

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

Esteemed Advisor
Posts: 5,543

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

Posted in reply to sasuser31

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.

PG
Contributor
Posts: 22

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

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.

 

 

Super User
Posts: 13,583

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

Posted in reply to sasuser31

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

Contributor
Posts: 22

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

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

 

Survey Data

suridicodeIdatestart eligibilityVenueAgeSex
100312/20/20189:38:2412591
150312/20/201810:58:2712462
200112/20/201812:19:2012351
250112/20/20183:52:1002182
300212/25/201812:35:2612331
350612/25/201813:07:36135512
400812/25/201813:24:59035251
450212/25/201816:07:42135622
500612/25/201818:24:19135431

 

 

 

Error Log:

P_ordersuridicodeidatestartNameo_valuen_value
12000112/20/201812:19:20surid200300
22000112/20/201812:19:20eligibility1
13000212/25/201812:35:26Venue0235
14000812/25/201813:24:59Age2552
15000612/25201818:24:19Sex12

 

 

I hope this helps!

Ask a Question
Discussion stats
  • 4 replies
  • 54 views
  • 0 likes
  • 3 in conversation