topic Re: Question about using Merge - to select records from a file - 1 to many in SAS Enterprise Guide

Question about using Merge - to select records from a file - 1 to many

QZBD5Z — Fri, 08 Jun 2012 17:35:05 GMT

I have a key file (File #2) , with a field that is also on file #1.

Using Merge I want to create a 3rd file of records from File #1 that share a key field matching records in File #2.

File #1 has multiple records with the key from one record in File #2, but only the first record is put into File #3.

I want all occurrences from File #2 that match File #1 in File #3.

How do I do that?

DATA ORDSSEL;

MERGE PROD(IN = INONE) KEYFILE(IN = INTWO) ;

BY VIN;

IF INONE AND INTWO THEN DO;

FILE THAIPROD;

PUT @1 ALL1 $CHAR11.

@12 VIN $CHAR17.

@29 ALL2 $CHAR49.

@78 TYPE $CHAR3.;

END;

Re: Question about using Merge - to select records from a file - 1 to many

Tom — Sat, 09 Jun 2012 14:23:03 GMT

You will need to provide some example data where it doesn't work.

I made some sample data with KEYFILE having unique rows for each VIN and PROD having multiple rows per VIN.

It worked as expected.

data prod;

input vin $ all1 $ all2 $ @@;

cards;

1 a b 1 c d 1 e f

3 a b 3 c d 3 e f

run;

data keyfile ;

input vin $ type $ @@ ;

cards;

1 one 2 two 3 three

run;

DATA ORDSSEL;

MERGE PROD(IN = INONE) KEYFILE(IN = INTWO) ;

BY VIN;

IF INONE AND INTWO THEN DO;

PUT (vin type all1 all2) ($CHAR4. $char7. $char6. $char6.);

END;

run;

1 one a b

1 one c d

1 one e f

3 three a b

3 three c d

3 three e f

Re: Question about using Merge - to select records from a file - 1 to many

QZBD5Z — Mon, 11 Jun 2012 14:46:34 GMT

Oops..... you are right....

I see my error while preparing the data to show you... the full VIN is not on every record.

Thanks for you time!