topic Re: Merge?? in SAS Procedures

Merge??

robertrao — Wed, 11 Sep 2013 15:27:35 GMT

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

102 sal

Table2

101

102

want

ID NAME

101 Jill

102 Sal

Thanks

Re: Merge??

AncaTilea — Wed, 11 Sep 2013 17:47:17 GMT

Hi.

What happens when you do;

data table3;

set table1;

by id;

if first.id;

run;

Is that what you need?

Anca.

Re: Merge??

PeteB — Thu, 12 Sep 2013 01:29:02 GMT

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;

Re: Merge??

Scott_Mitchell — Thu, 12 Sep 2013 09:20:57 GMT

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

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;