Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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;                              


Accepted Solutions
Solution
‎06-09-2012 10:23 AM
Super User
Super User
Posts: 6,367

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

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

View solution in original post


All Replies
Solution
‎06-09-2012 10:23 AM
Super User
Super User
Posts: 6,367

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

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

New Contributor
Posts: 2

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

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!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 184 views
  • 0 likes
  • 2 in conversation