Obsidian | Level 7

## simple solution sql

hello

I am looking for the simplest solution to the problem presented below

``````data A;
input id y;
cards;
1 50
2 30
3 30
4 60
5 70
6 80
7 70
2 30
run;

data B;
input id changeY;
cards;
3 300
4 472
5 111
6 2010
run;``````

result:   if A.id = B.id then y + changeY;

in table A

1 50
2 30
3 330
4 532
5 181
6 2810
7 70
2 30

7 REPLIES 7
Opal | Level 21

## Re: simple solution sql

``````proc sql;
update a
set y = sum(y, (select changeY from b where a.id=b.id));
quit;``````
PG
Obsidian | Level 7

## Re: simple solution sql

Ok thank you, unfortunately it did not work in this case:

``set y = y * (select changeY from b where a.id=b.id);``

Opal | Level 21

## Re: simple solution sql

Try

set y = y * coalesce((select changeY from b where a.id=b.id), 1);

PG
Super User

## Re: simple solution sql

A simple merge can do it:

``````data A;
input id y;
cards;
1 50
2 30
3 30
4 60
5 70
6 80
7 70
2 30
;

data B;
input id changeY;
cards;
3 300
4 472
5 111
6 2010
;

data want;
merge a b;
by id;
y = sum(y,changey);
drop changey;
run;``````

untested, posted from my tablet.

Quartz | Level 8

## Re: simple solution sql

If your actual datasets are quite large, the best way (IMO) is to do this is with a hash object.

``````data result;
if _n_=1 then do;
declare hash b(dataset:"b");
b.DefineKey("id");
b.DefineData("changeY");
b.DefineDone();
if 0 then set b;
end;

set a;

if b.find() = 0 then y = sum(y, changey);

drop changeY;
run;``````
Super User

## Re: simple solution sql

Even better would be to use MODIFY statement to update the existing A dataset instead of creating a new dataset.   If A is large that will reduce a lot of I/O and disk usage.  Risk is that you will no longer have the un-modified version of A.

Your case is like example 3 in this version of the documentation.  https://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n0g9jfr4x5hgsfn17gtma5547lt1.htm&doc...

``````data A;
if _n_=1 then do;
declare hash b(dataset:"b");
b.DefineKey("id");
b.DefineData("changeY");
b.DefineDone();
if 0 then set b(keep=changeY);
end;

modify A;

if 0=b.find() then do;
y = sum(y, changey);
replace;
end;

run;``````

Results:

```NOTE: There were 4 observations read from the data set WORK.B.
NOTE: There were 8 observations read from the data set WORK.A.
NOTE: The data set WORK.A has been updated.  There were 4 observations rewritten, 0 observations added and 0 observations deleted.

Obs    id       y

1      1      50
2      2      30
3      3     330
4      4     532
5      5     181
6      6    2090
7      7      70
8      2      30

```

Obsidian | Level 7

## Re: simple solution sql

Thank you for solving my problem and broadening the horizon :). I have one more problem (similar in my opinion) could you help me too?

``````data A;
informat datetime  datetime21.;
input datetime A B;
format datetime  datetime21.;
cards;
02JUN2003:21:58:00	1	8
02JUN2003:21:58:30	2	2
02JUN2003:21:59:00	5	3
02JUN2003:21:59:30	8	7
02JUN2003:22:00:00	3	2
02JUN2003:22:00:30	4	0
02JUN2003:22:01:00	8	0.8
02JUN2003:22:01:30	3	1
02JUN2003:22:02:00	2	7
02JUN2003:22:02:30	5	3
02JUN2003:22:03:00	2	5
02JUN2003:22:03:30	7	8
02JUN2003:22:04:00	9	7
02JUN2003:22:04:30	10	2
02JUN2003:23:05:00	6	6
02JUN2003:23:05:30	3	7
02JUN2003:23:06:00	7	6
run;

data B;
informat datetimeL  datetime21. datetimeR  datetime21.;
input datetimeL datetimeR move;
format datetimeL datetimeR  datetime21.;
cards;
02JUN2003:22:00:00	02JUN2003:22:59:59	1
02JUL2003:17:00:00	02JUL2003:17:59:59	1
01AUG2003:23:00:00	01AUG2003:23:59:59	3
run;``````

pseudocode: if A.datetime between B.datetimeL and B.datetimeP then A.datetime = INTNX('second',A.datetime,B.move * 60)

``````data Result;
informat datetime  datetime21.;
input datetime;
format datetime  datetime21.;
cards;
02JUN2003:21:58:00	1	8
02JUN2003:21:58:30	2	2
02JUN2003:21:59:00	5	3
02JUN2003:21:59:30	8	7
02JUN2003:23:00:00	3	2
02JUN2003:23:00:30	4	0
02JUN2003:23:01:00	8	0.8
02JUN2003:23:01:30	3	1
02JUN2003:23:02:00	2	7
02JUN2003:23:02:30	5	3
02JUN2003:23:03:00	2	5
02JUN2003:23:03:30	7	8
02JUN2003:23:04:00	9	7
02JUN2003:23:04:30	10	2
02JUN2003:23:05:00	6	6
02JUN2003:23:05:30	3	7
02JUN2003:23:06:00	7	6
run;``````