topic Re: Help in merging two datasets based condition. in SAS Programming

Help in merging two datasets based condition.

apxprdtr10 — Fri, 27 Apr 2018 18:12:22 GMT

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.

Re: Help in merging two datasets based condition.

ballardw — Fri, 27 Apr 2018 18:21:51 GMT

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

Re: Help in merging two datasets based condition.

apxprdtr10 — Fri, 27 Apr 2018 18:30:25 GMT

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

Here i want to merge both datasets.

dataset B

id x y

1 12 34

2 23 24

1 12 34

3 54 56

and so on.

Thanks.

Re: Help in merging two datasets based condition.

Reeza — Fri, 27 Apr 2018 18:39:28 GMT

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

Re: Help in merging two datasets based condition.

apxprdtr10 — Fri, 27 Apr 2018 18:41:54 GMT

could you help me with syntax code

Re: Help in merging two datasets based condition.

Reeza — Fri, 27 Apr 2018 18:52:43 GMT

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

Re: Help in merging two datasets based condition.

novinosrin — Fri, 27 Apr 2018 19:22:35 GMT

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

Re: Help in merging two datasets based condition.

kiranv_ — Fri, 27 Apr 2018 19:25:31 GMT

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

Re: Help in merging two datasets based condition.

Patrick — Sat, 28 Apr 2018 01:54:42 GMT

@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;

Re: Help in merging two datasets based condition.

s_lassen — Mon, 30 Apr 2018 10:40:00 GMT

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;

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