BookmarkSubscribeRSS Feed
sasuser31
Calcite | Level 5

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

4 REPLIES 4
PGStats
Opal | Level 21

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
sasuser31
Calcite | Level 5

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

@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
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 4 replies
  • 715 views
  • 0 likes
  • 3 in conversation