topic Direct Record/Observation Replacement in SAS Programming

Direct Record/Observation Replacement

BCNAV — Mon, 23 Dec 2019 16:25:18 GMT

I have a large aircraft/radar/billing database that I am going to need to query. The main index variable is billable_id....it is ALWAYS unique.

Now suppose I pull out a subset of the larger data, to correct missing information that I could use later. Is it possible to directly "copy back and overwrite" all of the subset dataset back into the main set? No new variables are added. I know this can be done in multiple steps, I'm just wondering if it can be done in SQL in one step.

Re: Direct Record/Observation Replacement

PaigeMiller — Mon, 23 Dec 2019 16:32:44 GMT

Do you have write access to the database?

What steps do you need to correct the data?? (Are you using other PROCs than SQL, or DATA steps)?

What do you mean by "one step"? At a minimum, you'd have to use SQL to extract the data, and another step to write the corrected data back to the database, but this could be a single PROC SQL call with two commands (a command to extract the data and a command to write) is that what you mean by "one step"?

Re: Direct Record/Observation Replacement

novinosrin — Mon, 23 Dec 2019 16:34:37 GMT

HI @BCNAV It's always good to post what you HAVE and WANT alongside explaining the business logic of your requirement.

If I understand you correctly, you basically want to MODIFY your dataset/table as opposed to creating a new temporary/permanent dataset. If yes, the natural approach would be to use MODIFY statement in Datastep or Proc SQL UPDATE statement to basically update the table in place. Proc SQL UPDATE is indeed common and there are gazillion examples online.

Also, any ANSI SQL UPDATE works in proc sql for the reason most SQL proprietary products have "update" functionality are consistent to conform to ANSI standards

Re: Direct Record/Observation Replacement

ballardw — Mon, 23 Dec 2019 17:13:03 GMT

My SQL update skills are lacking but the DATA step MODIFY or UPDATE look to be likely candidates.

Example:

data master;
   update master
          transaction
   ;
   by uniqueid;
run;

Where Transaction would have your corrected data.

Since BY is used the typical sort requirements are involved.

By default missing values in the transaction data set do not replace the value in the master data set but an option to force such is available if that is desired.

The transaction data set could have multiple values of the ID variable but master can only have it once.

Caution:

Any ID value in transaction not in Master is added to the output.

Any variable in transaction not in Master is also added to the output.

Re: Direct Record/Observation Replacement

BCNAV — Mon, 23 Dec 2019 19:08:12 GMT

this works!