Help using Base SAS procedures

Merge??

Reply
Super Contributor
Posts: 1,041

Merge??

Hi,

I have two tables like shown and the second table doesnt have duplicates by ID

Table2 is the dataset got after transposing and this is the wanted dataset but lacking names

I want to get the NAMES from Table1..while doing so i dont want duplicates as well in the TABLE2


TAble1

ID      Name

101    Jill

101   Jill

102  sal

102   sal

Table2

101

102

want

ID   NAME

101  Jill

102  Sal

Thanks

Super Contributor
Posts: 543

Re: Merge??

Posted in reply to robertrao

Hi.

What happens when you do;

data table3;

     set table1;

by id;

if first.id;

run;

Is that what you need?

Anca.

SAS Employee
Posts: 1

Re: Merge??

Posted in reply to robertrao

Hi,

If you really need to do a merge of both Table1 and Table2 and end up with you "wanted" data set you will need to merge and then dedup as follows.

Data Table3;

     merge Table1

                 Table2;

     by id;

Run;

Proc sort data=Table3 nodupkey;

     by id;

Run;

Alternatively you could use a number of ways to get to get to your "wanted" dataset without creating / using Table2 - like...

Proc sort data=Table1 (keep=id name) nodupkey

          out=Table3;

          by Id;

Run;

Or if you want so other stats try

Proc Summary data=Table1 nway missing;

     class id name;

     output out=Table3;

Run;

Super Contributor
Posts: 297

Re: Merge??

Hi RobertRao,

With a HASH TABLE you don't need to sort or dedupe the data:

/*CREATE EXAMPLE DATASETS*/

DATA TABLE1;

INFILE DATALINES;

INPUT ID NAME $;

DATALINES;

101 JILL

102 SAL

102 SAL

103 JIM

101 JILL

;

RUN;

DATA TABLE2;

INFILE DATALINES;

INPUT ID ;

DATALINES;

101

102

103

;

RUN;

DATA WANT;

IF 0 THEN SET TABLE1 TABLE2;

IF _N_ = 1 THEN DO;

  DECLARE HASH HH(DATASET : 'TABLE1' , ORDERED:'A');

  HH.DEFINEKEY('ID');

  HH.DEFINEDATA('ID','NAME');

  HH.DEFINEDONE();

END;

SET TABLE2;

  HH.FIND();

  OUTPUT;

RUN;

Ask a Question
Discussion stats
  • 3 replies
  • 195 views
  • 0 likes
  • 4 in conversation