BookmarkSubscribeRSS Feed
apxprdtr10
Calcite | Level 5

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.

9 REPLIES 9
ballardw
Super User

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

apxprdtr10
Calcite | Level 5

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_
Rhodochrosite | Level 12

 

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
Opal | Level 21

@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
Meteorite | Level 14

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
Super User

@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
Calcite | Level 5

could you help me with syntax code

Reeza
Super User

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
Tourmaline | Level 20
proc sql;
create table want as
select *
from b bb left join a aa
on bb.id=aa.id;
quit;

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
  • 805 views
  • 2 likes
  • 7 in conversation