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!
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.
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.