BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BCNAV
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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"?

--
Paige Miller
novinosrin
Tourmaline | Level 20

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 

ballardw
Super User

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.

BCNAV
Quartz | Level 8
this works!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 505 views
  • 0 likes
  • 4 in conversation