Proc SQL join? Maybe inner?

[ybz12003]

Hello, experts:

I have two datasets.   Dataset1 contains 10K obs and 1000 variables, including IDs, Status, etc.   Dataset2 contains 100 obs and two variables, IDs and Status.   I would like to merge the updated 'Status' in Dataset2 to Dataset1, by same IDs.    The IDs are characters, and the Status is numeric.    I think I could use Proc SQL join to merge, but not sure which join is best and which dataset should put first?  Please advice.

[Kurt_Bremser]

With 100 obs, simply create an informat (since you need to create a numeric value) and apply it during any step where you process the large dataset.

[novinosrin]

Hi @ybz12003 

Thumb rule:

1. Check for duplicate ID occurances in both datasets

2. If one has many ID's your larger one, and the other has only unique occurances of ID, it's a one to many join. 

So as long as it's one to one or one to many, you can play with merge/join/hash etc.

If you determine, both having multiple ID occurrences, then post your sample data and your expected output. Somebody can advice you at best!

[PGStats]

Do you need to create a new dataset? You could update the existing dataset with something like:

proc sql;
update dataset1 as a
set status = (select status from dataset2 where id=a.id)
where id in (select id from dataset2);
quit;