Problem in Merging two files in SAS University.

Reply
New Contributor
Posts: 4

Problem in Merging two files in SAS University.

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
Esteemed Advisor
Posts: 5,202

Re: Problem in Merging two files in SAS University.

SQL left join would be more straight forward.

Data never sleeps
New Contributor
Posts: 4

Re: Problem in Merging two files in SAS University.

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

Grand Advisor
Posts: 17,461

Re: Problem in Merging two files in SAS University.

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;

New Contributor
Posts: 4

Re: Problem in Merging two files in SAS University.

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

Esteemed Advisor
Posts: 6,706

Re: Problem in Merging two files in SAS University.

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Problem in Merging two files in SAS University.

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

Grand Advisor
Posts: 17,461

Re: Problem in Merging two files in SAS University.

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;

Esteemed Advisor
Posts: 6,706

Re: Problem in Merging two files in SAS University.

Since you have missing values, there must have been a problem copy/pasting the code. Inspect the data in the 2 inputfiles.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 5,005

Re: Problem in Merging two files in SAS University.

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;


Ask a Question
Discussion stats
  • 9 replies
  • 370 views
  • 0 likes
  • 5 in conversation