BookmarkSubscribeRSS Feed
mithuns22
Calcite | Level 5

Hi Folks, I'm new to SAS programming.  I'm trying to merge two files and i'm not able to apply if the condition is Many to one.

   merge  inputfile_1 (in=a) inputfile_2  ;

    by ID;

   if a ; --> When i use this statement, I get output like below:

This just matched the first ID and not rest of the ID's present in the file1. Please help me what I'm missing.

Here is my below example.

inputfile_1

IDAmountDescription
12345500,000Test 1
12345600,000Test 2
12345700,000Test 3
23456500,000Test 1
23456600,000Test 2
23456700,000Test 3
34567500,000Test 1
34567600,000Test 2
34567700,000Test 3
45678800,000Test 33

inputfile_2

IDAmount_newDescription_new
12345450,000Test 4
23456350,000Test 5
34567250,000Test 6

Desired Output :

IDAmountDescriptionAmount_newDescription_new
12345500,000Test 1450,000Test 4
12345600,000Test 2450,000Test 4
12345700,000Test 3450,000Test 4
23456500,000Test 1350,000Test 5
23456600,000Test 2350,000Test 5
23456700,000Test 3350,000Test 5
34567500,000Test 1250,000Test 6
34567600,000Test 2250,000Test 6
34567700,000Test 3250,000Test 6
45678800,000Test 33     

Output I get :

IDAmountDescriptionAmount_newDescription_new
12345500,000Test 1450,000Test 4
12345600,000Test 2
12345700,000Test 3
23456500,000Test 1350,000Test 5
23456600,000Test 2
23456700,000Test 3
34567500,000Test 1250,000Test 6
34567600,000Test 2
34567700,000Test 3
45678800,000Test 33
9 REPLIES 9
LinusH
Tourmaline | Level 20

SQL left join would be more straight forward.

Data never sleeps
mithuns22
Calcite | Level 5

Thanks for your Prompt reply Linus Hjorth. But, I have the data in Excel file.

Reeza
Super User

How does the fact that the data is in Excel matter? Once you import the data into SAS they're SAS datasets and I agree with Linus - use a left join.

I don't even think there's an easy way for a data step merge to accomplish this.

You may want to list out your variables but the code should look something like this:

proc sql;

create table want as

select a.*, b.*

from table1 as a

left join table2 as b

on a.id=b.id;

quit;

mithuns22
Calcite | Level 5

Thank you Reeza. I will try this option and keep you posted. Also, thanks for your patience on providing a solution to newbie.

Kurt_Bremser
Super User

There must be more to your code in the data step than you posted

This code:

data in1;

infile cards dlm='09'x;

input ID amount :comma. description :$7.;

cards;

12345    500,000    Test 1

12345    600,000    Test 2

12345    700,000    Test 3

23456    500,000    Test 1

23456    600,000    Test 2

23456    700,000    Test 3

34567    500,000    Test 1

34567    600,000    Test 2

34567    700,000    Test 3

45678    800,000    Test 33

;

run;

data in2;

infile cards dlm='09'x;

input ID amount_new :comma. description_new :$7.;

cards;

12345    450,000    Test 4

23456    350,000    Test 5

34567    250,000    Test 6

;

run;

data want;

merge in1 (in=a) in2;

by ID;

if a ;

run;

gave me this result:

12345    500000    Test 1    450000    Test 4

12345    600000    Test 2    450000    Test 4

12345    700000    Test 3    450000    Test 4

23456    500000    Test 1    350000    Test 5

23456    600000    Test 2    350000    Test 5

23456    700000    Test 3    350000    Test 5

34567    500000    Test 1    250000    Test 6

34567    600000    Test 2    250000    Test 6

34567    700000    Test 3    250000    Test 6

45678    800000    Test 33    .  

which is exactly how the data step merge should work.

mithuns22
Calcite | Level 5

Hi KurtBremser,

I executed the same code from above in SAS University, here is my output.

Attached my Sample code and result for your reference. Is results variation is because of the versions?

sascode.pngresult.png

Reeza
Super User

ALWAYS check ALL your steps.

Run a proc print on IN1 and IN2 from your code above and see what the output is.

Try this version which works for me in SAS UE:

data in1;

input ID amount :comma. description :$7.;

cards;

12345    500,000    Test 1

12345    600,000    Test 2

12345    700,000    Test 3

23456    500,000    Test 1

23456    600,000    Test 2

23456    700,000    Test 3

34567    500,000    Test 1

34567    600,000    Test 2

34567    700,000    Test 3

45678    800,000    Test 33

;

run;

proc print data=in1; run;

data in2;

input ID amount_new :comma. description_new :$7.;

cards;

12345    450,000    Test 4

23456    350,000    Test 5

34567    250,000    Test 6

;

run;

proc print data=in2; run;

data want;

merge in1 (in=a) in2;

by ID;

if a ;

run;

proc print data=want; run;

Astounding
PROC Star

Is it possible that your INPUTFILE_1 already contains AMOUNT_NEW and DESCRIPTION_NEW?  That would be consistent with the results you are seeing, and those variables could be dropped as late as on the MERGE statement:

merge inputfile_1 (in=1 drop=amount_new description_new) inputfile_2;


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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 835 views
  • 0 likes
  • 5 in conversation