topic Re: Update unmatched missing values from left join in SAS Programming

Update unmatched missing values from left join

SK_11 — Tue, 23 Feb 2021 23:06:17 GMT

I have the following

data have1;
input Grp cum ;
datalines;
1 2
2 4
3 6
4 8
5 10
6 12
;
run;

data have2;
input id ;
datalines;
1
2
3
4
5
6
7
8
9
10
11
12
;
run;

proc sql;
Create table want as
Select a.Id,
b.Grp
from have2 as a
left join have1 as b
on a.id=b.Cum
;

The above gives the output as

id	Grp
1	.
2	1
3	.
4	2
5	.
6	3
7	.
8	4
9	.
10	5
11	.
12	6

I would like to get this:

id	Grp
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	5
10	5
11	6
12	6

Thanks for your help in advance.

Re: Update unmatched missing values from left join

qoit — Wed, 24 Feb 2021 00:49:31 GMT

SQL is not really the solution for this but here you go:

data have1;
	input Grp cum;
	datalines;
1 2
2 4
3 6
4 8
5 10
6 12
;
run;

data have2;
	input id;
	datalines;
1
2
3
4
5
6
7
8
9
10
11
12
;
run;

proc sql;
	Create table want (keep=id grp) as
		Select a.Id,
			b.Grp as grp_,
		case 
			when b.grp is missing then monotonic() - 1 
		end 
	as col,
		case 
			when b.grp is missing then sum(1,calculated col) 
			else b.grp 
		end 
	as grp
		from have2 as a
			left join have1 as b
				on a.id=b.Cum;
quit;

Re: Update unmatched missing values from left join

Tom — Wed, 24 Feb 2021 00:57:02 GMT

What is the reasoning that says ID=1 should be matched to GRP=1? I do not see the connection.

Re: Update unmatched missing values from left join

SK_11 — Wed, 24 Feb 2021 01:22:16 GMT

There is no relationship between group and id. This is a mere representation of more than 100K rows.

Re: Update unmatched missing values from left join

SK_11 — Wed, 24 Feb 2021 03:30:59 GMT

To remove the confusion here is the updated data set.

data have1;
input Grp cum ;
datalines;
1 3252
2 6752
3 10252
4 13752
5 17252
6 20752
;
run;

data have2;
do i=1 to 20000;
id=i;output;
end;
drop i;
run;

Re: Update unmatched missing values from left join

SK_11 — Wed, 24 Feb 2021 03:32:57 GMT

Thanks for your quick reply, I have updated the dataset to clear any confusion.

Re: Update unmatched missing values from left join

Tom — Wed, 24 Feb 2021 04:24:36 GMT

You must have had some algorithm in your mind when went from your two example input datasets to your desired output dataset.

What was the logic that said ID=1 was part of GRP=1 instead of GRP=2 or 3 or 3457?

Re: Update unmatched missing values from left join

SK_11 — Wed, 24 Feb 2021 04:34:17 GMT

I am trying to fill up the missing group values when I left join. The desired output will be all ids less than cum should have the same group value.

Re: Update unmatched missing values from left join

Tom — Wed, 24 Feb 2021 04:48:47 GMT

@SK_11 wrote:

I am trying to fill up the missing group values when I left join. The desired output will be all ids less than cum should have the same group value.

There must be more to it than that. If HAVE1 is

data have1;
  input Grp cum;
datalines;
1 2
2 4
3 6
4 8
5 10
6 12
;

The ID=1 will be a member of all 6 GRP values since one is less than 2 and also less than 4 and less then 6 etc.

Why would you want to try to force a solution to this problem using SQL?

Re: Update unmatched missing values from left join

Tom — Wed, 24 Feb 2021 04:47:10 GMT

Do you just want to do this?

Given this input:

data have1;
  input Grp cum;
datalines;
1 2
2 4
3 6
4 8
5 10
6 12
;

Run this data step:

data want;
  set have1;
  do id=sum(lag(cum),1) to cum;
     output;
  end;
run;

Result:

Obs    Grp    cum    id

  1     1       2     1
  2     1       2     2
  3     2       4     3
  4     2       4     4
  5     3       6     5
  6     3       6     6
  7     4       8     7
  8     4       8     8
  9     5      10     9
 10     5      10    10
 11     6      12    11
 12     6      12    12

Re: Update unmatched missing values from left join

SK_11 — Wed, 24 Feb 2021 04:48:36 GMT

Thanks for your quick reply. Any solutions would be fine. I have given a SQL example to show the missing values when I left join. I am interested to fill up the missing values of the group column where ids are less than the matched ids. I have updated the example data set as
data have1;
input Grp cum ;
datalines;
1 3252
2 6752
3 10252
4 13752
5 17252
6 20752
;
run;

data have2;
do i=1 to 20000;
id=i;output;
end;
drop i;
run;

Re: Update unmatched missing values from left join

Tom — Wed, 24 Feb 2021 05:50:42 GMT

What does HAVE2 add to the problem? Are you just trying to set an upper bound on the value of ID?

data have1;
input Grp cum ;
datalines;
1 3252
2 6752
3 10252
4 13752
5 17252
6 20752
;
data want;
  set have1;
  do id=sum(lag(cum),1) to min(cum,20000);
     output;
  end;
run;
proc freq data=want;
  tables grp ;
run;

The FREQ Procedure

                                Cumulative    Cumulative
Grp    Frequency     Percent     Frequency      Percent
--------------------------------------------------------
  1        3252       16.26          3252        16.26
  2        3500       17.50          6752        33.76
  3        3500       17.50         10252        51.26
  4        3500       17.50         13752        68.76
  5        3500       17.50         17252        86.26
  6        2748       13.74         20000       100.00

Re: Update unmatched missing values from left join

Tom — Wed, 24 Feb 2021 06:07:20 GMT

If you want to "join" HAVE1 and HAVE2 then first convert HAVE1 to something with a range of values of ID.

data groups;
  set have1;
  by cum;
  min=sum(lag(cum),1);
  max=cum;
  keep grp min max;
run;

Now you can join HAVE2 with GROUPS and assign the GRP value to each observation in HAVE2.

proc sql ;
create table want as
  select * from have2 a inner join groups b
  on a.id between b.min and b.max
;
quit;

Re: Update unmatched missing values from left join

SK_11 — Wed, 24 Feb 2021 08:22:39 GMT

Thanks a lot Tom