BookmarkSubscribeRSS Feed
bunnycao
Calcite | Level 5

Hi,

I'm wondering what is the best way to update a master dataset from several transactional dataset.

The situation is , master contains 1728 records, and i have 10 different transactional dataset to update this master dataset ( one for each month), the goal is to see after 10 months, how these 1728 records would look like. so if the transac data contains more records than master, don't add them. if contains less records and non-matching records, only update the matching ones and leave the unmatched stay the same. I've tried update statement together with merge statement, part of my code is as follows:

data tmp#

  update result_start

            result#

  by ERFAC:

  run;

data new#

    merge result_start(in=a)

             tmp&num(in=b);

   by ERFAC;

   if a and b ;

run;

so here result_start is my master data, for each iteration, i want to use result&num to update result_start, however, the update statement would add new records onto master data, so i have to do a merge step to get rid of the unwanted records and hopefully keep the master data updated.

Could anyone tell me if i was doing anything wrong here ,and if there is a better way to do this.

Thanks!

2 REPLIES 2
Astounding
PROC Star

Untested, but I imagine you could do this in one step and eliminate the MERGE:

data tmp#

  update result_start (in=in_master)

            result#

  by ERFAC:

  if in_master=0 then delete;

  run;

Tom
Super User Tom
Super User

You do know how the UPDATE statement works right?  How it handles missing values and repeats of BY variables is very different than the MERGE statement.

The UPDATE statement is for applying transactions.  It requires exactly two datasets.  But you could make a view that combines all of your transactions into one and then apply them.

data transactions/ view=transactions  ;

  set result1 - result10 ;

  by id;

run;

data want ;

  update master transactions;

  by id;

run;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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