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

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

variathu — Thu, 14 Sep 2017 19:20:04 GMT

i have two datasets namely data1 and data2

data data1;

input x;

cards;

;

data data2;

input c;

cards;

;

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.

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

ballardw — Thu, 14 Sep 2017 19:40:31 GMT

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

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

novinosrin — Thu, 14 Sep 2017 19:45:12 GMT

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;

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

PeterClemmensen — Thu, 14 Sep 2017 19:45:17 GMT

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;

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

mkeintz — Thu, 14 Sep 2017 19:45:21 GMT

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.

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

Reeza — Thu, 14 Sep 2017 19:45:47 GMT

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.

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

variathu — Thu, 14 Sep 2017 19:58:38 GMT

thank you very much 😄

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

Haikuo — Thu, 14 Sep 2017 20:00:10 GMT

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

data want;
set data1;
n=1;
set data2 point= n;
run;

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

mkeintz — Thu, 14 Sep 2017 20:06:47 GMT

I believe there is a performance problem with the direct-access method (set data2 point=n;). 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;

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

Haikuo — Thu, 14 Sep 2017 20:32:48 GMT

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

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

SAS_inquisitive — Thu, 14 Sep 2017 20:39:01 GMT

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