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;
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
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
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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.