BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
QZBD5Z
Calcite | Level 5

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;                              

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

2 REPLIES 2
Tom
Super User Tom
Super User

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

QZBD5Z
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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