Help in merging two datasets based condition.

[apxprdtr10]

Hi

I have two data set having 2000 rows and 3 columns in dataset1 and 10 rows and 1 variable in dataset2.

dataset2 have variable name configuration from 1 to 10 and dataset1 have variable name configuration from 1 to 10 but it has many number of times.

I want to merge both dataset, where i want whenever from, dataset2 get value of configuration from 1 to 10 it keeps the value of configuration and respective variables from dataset1.

Suggest me some method.

Thanks.

[ballardw]

Please provide explicit examples of the two data sets and the desired result.

[apxprdtr10]

dataset A                                                                     dataset B                                              

 id      x      y                                                                 id

 1       12   34                                                                1

 2       23  24                                                                  2

 3       54  56                                                                  2 

 4      85   45                                                                  1

 5     98    92                                                                   3

                                                                                       2

                                                                                      1

                                                                                       4

                                                                                         5

Here i want to merge both datasets.

dataset B

id x y

1 12 34

2 23 24

2 23 24

1 12 34

3 54 56

and so on.

Thanks.

[kiranv_]

proc sql;
select B.id, a.x, a.y 
from dataset_b b
inner join dataset_a a
on b.id = a.id;

I am not  very sure what you are looking for but I guess something like above might work

[Patrick]

@apxprdtr10

data a;
  input id x y;
  datalines;
1 12 34 
2 23 24 
3 54 56 
4 85 45 
5 98 92 
;
run;

data B;
  input id;
  sortOrder=_n_;
  datalines;
1
2
2 
1
3
2
1
4
5
;
run;

proc sql;
  create table want as
  select 
    b.sortOrder,
    a.*
  from a inner join b
    on a.id=b.id
  order by b.sortOrder
  ;
quit;

[s_lassen]

If you want the data in the order shown (as in the B table), you cannot use SQL the way some have suggested. The most obvious solutions seem to use a hash or an index; the index solution is probably the simplest to program:

data A;

  input id x y;

cards;

1 12 34

2 23 24

3 54 56

4 85 45

5 98 92

;run;

data B;

  input id;

cards;

1

2

2

1

3

2

1

4

5

;run;

proc sql;

  create index id on a(id);

quit;

data want;

  set b;

  set a key=id/unique;

  if not _iorc_;

run;

The "if not _iorc_" line says that you do not want any output if the ID is not found in A.

If you do want the data from B anyway, you should set X and Y missing to show that no data were found in A:

if _iorc_ then do;

  call missing(x,y);

  _error_=0;

  end;

I put the _error_=0 in because in that case, you probably do not want an error message when there is no data in A.

[Reeza]

---

@apxprdtr10 wrote:

Hi

I have two data set having 2000 rows and 3 columns in dataset1 and 10 rows and 1 variable in dataset2.

dataset2 have variable name configuration from 1 to 10 and dataset1 have variable name configuration from 1 to 10 but it has many number of times.

I want to merge both dataset, where i want whenever from, dataset2 get value of configuration from 1 to 10 it keeps the value of configuration and respective variables from dataset1.

Suggest me some method.

Thanks.

---

When doing conditional joins, SQL or Hash are your best options IMO. 

[apxprdtr10]

could you help me with syntax code

[Reeza]

Not without data, general questions = general answer. 

Specific questions with example data and a clear illustration of the output will get you example programs.

---

@apxprdtr10 wrote:

could you help me with syntax code

---

[novinosrin]

proc sql;
create table want as
select *
from b bb left join a aa
on bb.id=aa.id;
quit;