topic Re: Merging with a range in SAS Data Management

Merging with a range

Demographer — Fri, 25 Oct 2019 05:44:40 GMT

Hi, I want to include the variable X of dataset 2 to the corresponding ID of dataset 1. The ID of dataset 2 is however a range. In the example below, the X for ID=0.56 should be 4, because the ID is below 0.73 and above 0.49, X for ID=0.22 should be 44, etc.

Dataset 1
ID	(expected X)
0.56	4
0.22	44
0.17	44
0.09	48
0.90	44
0.37	22
0.78	44
0.22	44
0.53	4
0.12	48
0.52	4
0.32	22

Dataset 2
ID	X
0.05	37
0.09	2
0.16	48
0.31	44
0.38	22
0.49	38
0.73	4
0.77	27
0.92	44
0.96	12
0.99	1
1.00	5

Not really sure if the merge statement is suitable for this.

Re: Merging with a range

PeterClemmensen — Fri, 25 Oct 2019 06:11:10 GMT

Here is one way

data1;
input ID;
datalines;
0.56
0.22
0.17
0.09
0.90
0.37
0.78
0.22
0.53
0.12
0.52
0.32
;

data data2;
input ID X;
datalines;
0.05 37
0.09 2
0.16 48
0.31 44
0.38 22
0.49 38
0.73 4
0.77 27
0.92 44
0.96 12
0.99 1
1.00 5
;

data temp;
    set data2;
    id_from=lag(id);
    rename id=id_to;
run;

data want(keep=ID X);
    if _N_=1 then do;
        declare hash h(dataset:'temp');
        h.definekey(all:'Y');
        h.definedone();
        declare hiter hi('h');
    end;

    set data1;
    if 0 then set temp;

  	do rc=hi.first() by 0 while (rc=0);
        if id_from <= ID <= id_to then leave;
		rc=hi.next();
	end;
run;

Result:

Re: Merging with a range

Kurt_Bremser — Fri, 25 Oct 2019 07:48:15 GMT

As an alternative, create a value format with ranges from dataset 2, and use it on dataset 1.

Re: Merging with a range

Demographer — Fri, 25 Oct 2019 07:52:02 GMT

How does this work?

Re: Merging with a range

PeterClemmensen — Fri, 25 Oct 2019 08:14:42 GMT

Something like this

data temp(keep=fmtname start end label);
    merge data2(rename=id=start)
          data2(firstobs=2 keep=id X rename=(id=end X=label));
    retain fmtname 'range';
    if end ne .;
run;

proc format library=work cntlin=temp;
run;

data want;
    set data1;
    X=put(ID, range.);
run;

Re: Merging with a range

Astounding — Sat, 26 Oct 2019 23:06:12 GMT

It sounds like you should combine the two data sets to generate this type of program:

data want;
set dataset2;
if ID <= 0.05 then X=37;
else if ID <= 0.09 then X=2;
else if ID <= 0.16 then X=48;
......
run;

Here's a way to utilize your two data sets to construct and execute such a program.

data _null_;
call execute('data want; set dataset2;');
do until (done);
   set dataset1 end=done;
   if_then = catx(' ', 'if ID <=', ID, 'then X=', X, ';');
   call execute(if_then);
   if done=0 then call execute('else');
end;
call execute('run;');
stop;
run;

It's untested code, so see if it causes any problems vs. does the job properly.