Merging one variable in a data set with all the values in a different data set

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Merging one variable in a data set with all the values in a different data set

i have two datasets namely data1 and data2

 

data data1;

input x;

cards;

12

24

45

;

data data2;

input c;

cards;

10

;

 

i want another dataset namely data3 to contain

   x     c

  12   10

  24   10

  45   10

 

Any help would be appreciated. I'm currently using sas 9.4 on windows.


Accepted Solutions
Solution
‎09-14-2017 03:58 PM
Super User
Posts: 11,517

Re: Merging one variable in a data set with all the values in a different data set

proc sql;
  create table want as
  select data1.x, data2.c
  from data1,data2
  ;
quit;

View solution in original post


All Replies
Solution
‎09-14-2017 03:58 PM
Super User
Posts: 11,517

Re: Merging one variable in a data set with all the values in a different data set

proc sql;
  create table want as
  select data1.x, data2.c
  from data1,data2
  ;
quit;
PROC Star
Posts: 343

Re: Merging one variable in a data set with all the values in a different data set

proc sql carterian or datastep one to one merge:

 


data data1;
input x;
cards;
12
24
45
;
data data2;
input c;
cards;
10
;

proc sql;
create table want as
select x,c
from data1,data2;
quit;

 

data want;
set data1;
if _n_=1 then set data2;
run;

Occasional Contributor
Posts: 5

Re: Merging one variable in a data set with all the values in a different data set

Posted in reply to novinosrin

thank you very much :-D 

PROC Star
Posts: 847

Re: Merging one variable in a data set with all the values in a different data set

Like this?

 

data data1;
input x;
cards;
12
24
45
;
data data2;
input c;
cards;
10
;

proc sql;
   create table data3 as
   select data1.*, data2.c
   from data1 join data2 on c;
quit;
Trusted Advisor
Posts: 1,058

Re: Merging one variable in a data set with all the values in a different data set

You can take advantage of the fact the any variables in the program data vector that result from a SET (or MERGE) statement are automatically ratained until the next iteration of a SET (or MERGE) of the same data set.

 

So make sure you only SET DATA2 for one iteraction of the data step:

 

data want; 
  set data1;
  if _n_=1 then set data2;
run;

 

The above only reads in DATA2 in the first iteration of the data step, while DATA1 is read in for every iteration.  The data step ends when a SET attempts to read beyond the end of an incoming data set.

Super User
Posts: 20,203

Re: Merging one variable in a data set with all the values in a different data set

And the data step method:

 

data want;
set data1;
if _n_=1 then set data2;
run;

variathu wrote:

i have two datasets namely data1 and data2

 

data data1;

input x;

cards;

12

24

45

;

data data2;

input c;

cards;

10

;

 

i want another dataset namely data3 to contain

   x     c

  12   10

  24   10

  45   10

 

Any help would be appreciated. I'm currently using sas 9.4 on windows.


 

Respected Advisor
Posts: 3,157

Re: Merging one variable in a data set with all the values in a different data set

Another data step approach besides the classical solution that many have mentioned:

 

data want;
set data1;
n=1;
set data2 point= n;
run;
Trusted Advisor
Posts: 1,058

Re: Merging one variable in a data set with all the values in a different data set

I believe there is a performance problem with the direct-access method (set data2 point=nSmiley Wink.  The first DATA _NULL_ below took 5.1 seconds on my windows machine, while the second (using the "point=" technique) took 28.8 seconds.

 

data data1;
  do x=1 to 100000000;output;end;
run;
data data2;
  c=0;
run;

data _null_;
  set data1;
  if _n_=1 then set data2;
run;

data _null_;
  set data1;
  retain n 1;
  set data2 point=n;
run;
Respected Advisor
Posts: 3,157

Re: Merging one variable in a data set with all the values in a different data set

Yes, looks like the unconditional execution is the culprit?

23         data _null_;
24           set data1;
25           if _n_=1 then set data2;
26         run;

NOTE: There were 100000000 observations read from the data set WORK.DATA1.
NOTE: There were 1 observations read from the data set WORK.DATA2.
NOTE: DATA statement used (Total process time):
      real time           3.43 seconds
      cpu time            3.40 seconds
      

27         
28         data _null_;
29           set data1;
30         if _n_=1 then  set data2 point=_n_;
31         
32         run;

NOTE: There were 100000000 observations read from the data set WORK.DATA1.
NOTE: DATA statement used (Total process time):
      real time           3.48 seconds
      cpu time            3.44 seconds
Super Contributor
Posts: 271

Re: Merging one variable in a data set with all the values in a different data set

Take the advantage of automatic retention. Read pitfalls of automatic retention in this paper (http://analytics.ncsu.edu/sesug/2015/CC-34.pdf)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 288 views
  • 7 likes
  • 8 in conversation