Help using Base SAS procedures

Join tables

Reply
New Contributor
Posts: 2

Join tables

I have the following dataset called "Agearank" containing 2,042 observations.

Capture.PNG

I also have another dataset called "Agea" containing 28,031 observations.

f.PNG

I would like to join "Agea" into "Agearank" by IN_PUMA_ID. My goal is to attach the variable PWPUMAS (i.e., Super-PUMA of work) in the "Agea" dataset to the "Agearank" dataset. In the newly created dataset (e.g., new), I would only like to have 2,042 observations based on the "Agearank" dataset. How should I write the SAS code? Thank you.

Regular Contributor
Posts: 180

Re: Join tables

Posted in reply to MichaelLin

There are many values of the variable PWPUMAS for each IN_PUMA_ID. You need to chose what of them to attach. Maybe MAX(PWPUMAS) ?

Regular Contributor
Posts: 180

Re: Join tables

Posted in reply to MichaelLin

Try this to attach the value of MAX(PWPUMAS) by each IN_PUMA_ID group:

proc sql noprint;

  create table new as

  select A.*, MPWPUMAS as PWPUMAS

  from Agearank A

  left join

    (select distinct IN_PUMA_ID, MAX(PWPUMAS) as MPWPUMAS

     from Agea B

     group by IN_PUMA_ID)

  on (A.IN_PUMA_ID = B.IN_PUMA_ID)

  order by 1;

quit;

CTorres

New Contributor
Posts: 2

Re: Join tables

Thank you for your reply.

Suppose PWPUMAS with values of 0 means the data are missing for those observations and let's assume we have deleted them from the dataset "Agea". Accordingly, each observation's PWPUMAS would have a unique value. If this is the case, how to write the SAS code for the question I asked? Thanks!

Cheers,

Michael

Regular Contributor
Posts: 180

Re: Join tables

Posted in reply to MichaelLin

The same code works OK with the new supposition: if all values of PWPUMAS are the same then MAX(PWPUMAS) is this value for each IN_PUMA_ID

You can also use the following code assuming both files are sorted by IN_PUMA_ID and PWPUMAS has the same value for each IN_PUMA_ID:

Data new;

  merge Agearank (in=in_rank) Agea (keep=IN_PUMA_ID PWPUMAS);

  by IN_PUMA_ID;

  if in_rank;

run;

CTorres

Super User
Posts: 11,343

Re: Join tables

Posted in reply to MichaelLin

WHICH value of PWPUMAS do you want from the second table? Your example data shows ID 0100100 with values of 300 and 0? What is the rule for selecting the single value of PWPUMAS from Agea when there are multiple values?

Ask a Question
Discussion stats
  • 5 replies
  • 321 views
  • 0 likes
  • 3 in conversation