Need to make changes in master_file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Need to make changes in master_file

Hi Team,

I have a master data set having more than 10 million records. Let’s say it has 11 variables (unique_key and field1-field10). I need to update/add some of the fields from a different file.

Master_File

Unique_Key

Field1

Field2

Field3

Field4

Field5

Field6

Field7

Field8

Field9

Field10

ABC2

ISD

50

100

  1. 0.5

100

50

4

1

  1. 0.25

50

ABC3

ISD

100

150

  1. 0.5

175

  1. 87.5

7

1

  1. 0.25

50

ABC4

ISD

150

200

  1. 0.5

250

125

10

1

  1. 0.25

50

ABC5

ISD

200

250

  1. 0.5

325

  1. 162.5

13

1

  1. 0.25

50

ABC6

ISD

250

300

  1. 0.5

400

200

16

1

  1. 0.25

50

ABC7

ISD

300

350

  1. 0.5

475

  1. 237.5

19

1

  1. 0.25

50

ABC8

ISD

350

400

  1. 0.5

550

275

22

1

  1. 0.25

50

ABC9

ISD

400

450

  1. 0.5

625

  1. 312.5

25

1

  1. 0.25

50

ABC10

ISD

450

500

  1. 0.5

700

350

28

1

  1. 0.25

50

Now I have another file having an additional field as Type which will have the value of either AMEND or ADD.

Another_File

Type

Unique_Key

Field1

Field2

Field3

Field4

Field5

Field6

Field7

Field8

Field9

Field10

AMEND

ABC2

100

  1. 0.75

200

100

AMEND

ABC3

300

  1. 0.25

4

100

AMEND

ABC4

350

  1. 0.25

7

100

AMEND

ABC5

400

  1. 0.25

10

100

ADD

ABC21

ISD

450

50

  1. 0.5

475

  1. 237.5

19

1

  1. 0.25

50

ADD

ABC22

ISD

500

100

  1. 0.5

550

275

22

1

  1. 0.25

50

ADD

ABC23

ISD

550

150

  1. 0.5

625

  1. 312.5

25

1

  1. 0.25

50

If the type is Amend than I want the detail of this unique_key to be updated from this file to the master file. If the value is missing in another_file for any of the field than I want the master_file to retain the original value.  And if the type is ADD then I simply want that record to be added to the master_file.

Any help on this is much appreciated!

Many thanks,

SAS Learner


Accepted Solutions
Solution
‎07-13-2013 09:05 PM
Super User
Posts: 5,505

Re: Need to make changes in master_file

Posted in reply to saslearner2

SAS actually has a tool to do this, so that you don't even need TYPE in your data set.  Try:

data want;

update master_file another_file (drop=type);

by id;

run;

Try it on a small sample of data such as your samples above to make sure it does what you need.

Good luck.

View solution in original post


All Replies
Solution
‎07-13-2013 09:05 PM
Super User
Posts: 5,505

Re: Need to make changes in master_file

Posted in reply to saslearner2

SAS actually has a tool to do this, so that you don't even need TYPE in your data set.  Try:

data want;

update master_file another_file (drop=type);

by id;

run;

Try it on a small sample of data such as your samples above to make sure it does what you need.

Good luck.

Occasional Contributor
Posts: 5

Re: Need to make changes in master_file

Posted in reply to Astounding

thanks Astounding,

It does work, now the only issue is that it requires me to first sort the data by unique_key. Since my actual master_data is huge so is there a way to avoid sorting things. Otherwise I am happy to use this as it is very simple.

Many thanks once again for your help!

Regards,

SAS Learner

Respected Advisor
Posts: 4,923

Re: Need to make changes in master_file

Posted in reply to saslearner2

SAS doc states:


The data sets listed in the UPDATE statement must be sorted by the values of the variables listed in the BY statement, or they must have an appropriate index.

Assuming that your huge master dataset is indexed by unique_key, you only need a similar index on your other file and it should work without sorting.

PG

PG
Super User
Posts: 5,505

Re: Need to make changes in master_file

Posted in reply to saslearner2

saslearner2,

Well, you have a few items to consider then.

Why should your master data set remain unsorted?  Perhaps it makes sense to sort it once, then keep it in order from that point forward.  Certainly there are advantages to having sorted data.  For example, UPDATE would illustrate two advantages.  First, it would keep the master data set in order.  Second, it would not require you to be accurate in your designation of ADD vs. AMEND.

If you decide to go with unsorted data, there are ways to make it happen.  However, they may require you to expand your SAS skillset.  One approach would be to create separate data sets for the ADD vs. AMEND records.  It would be simple enough to use PROC APPEND on the ADD subset.  But processing the AMEND subset would be more difficult.  One possibility:  Use the AMEND records as input to a DATA set that will write out IF/THEN statements to a separate file.  Then process the master data set in another DATA step and %INCLUDE the file holding the IF/THEN statements.

First step is to decide on an approach, and only then does the programming begin.

Good luck.

Occasional Contributor
Posts: 5

Re: Need to make changes in master_file

Posted in reply to Astounding

Thanks Astounding - It does make sense of to keep the things simple I will sort the master data and the other file. Rgds, SAS learner

Super Contributor
Posts: 297

Re: Need to make changes in master_file

Posted in reply to saslearner2

You can do it like this.

DATA MASTER_FILE;

INPUT UNIQUE_KEY $ FIELD1 $ FIELD2 FIELD3 FIELD4 FIELD5 FIELD6 FIELD7 FIELD8 FIELD9 FIELD10 ;

DATALINES;

ABC2 ISD 50 100 0.5 100 50 4 1 0.25 50

ABC3 ISD 100 150 0.5 175 87.5 7 1 0.25 50

ABC4 ISD 150 200 0.5 250 125 10 1 0.25 50

ABC5 ISD 200 250 0.5 325 162.5 13 1 0.25 50

ABC6 ISD 250 300 0.5 400 200 16 1 0.25 50

ABC7 ISD 300 350 0.5 475 237.5 19 1 0.25 50

ABC8 ISD 350 400 0.5 550 275 22 1 0.25 50

ABC9 ISD 400 450 0.5 625 312.5 25 1 0.25 50

ABC10 ISD 450 500 0.5 700 350 28 1 0.25 50

;

RUN;

DATA ANOTHER_FILE;

INFILE DATALINES DELIMITER="," TRUNCOVER;

INPUT UNIQUE_KEY $ FIELD1 $ FIELD2 FIELD3 FIELD4 FIELD5 FIELD6 FIELD7 FIELD8 FIELD9 FIELD10;

DATALINES;

ABC2, ,100,.,0.75,.,200,.,.,,.,100

ABC3, ,300,.,0.25,.,.,4,.,.,100

ABC4, ,350,.,0.25,.,.,7,.,.,100

ABC5, ,400,.,0.25,.,.,10,.,.,100

ABC21,ISD,450,50,0.5,475,237.5,19,1,0.25,50

ABC22,ISD,500,100,0.5,550,275,22,1,0.25,50

ABC23,ISD,550,150,0.5,625,312.5,25,1,0.25,50

;

RUN;

PROC SORT DATA= MASTER_FILE;

  BY UNIQUE_KEY;

RUN;

PROC SORT DATA= ANOTHER_FILE;

  BY UNIQUE_KEY;

RUN;

DATA WANT;

  UPDATE MASTER_FILE ANOTHER_FILE;

  BY  UNIQUE_KEY;

RUN;

Super Contributor
Posts: 297

Re: Need to make changes in master_file

Posted in reply to Scott_Mitchell

After posting this I see astounding has already replied.  My apologies for reposting the same solution.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 245 views
  • 3 likes
  • 4 in conversation