topic Re: simple solution sql in SAS Programming

simple solution sql

makset — Sun, 19 Jul 2020 18:05:03 GMT

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

Thank you for your help

Re: simple solution sql

PGStats — Sun, 19 Jul 2020 18:18:02 GMT

proc sql;
update a
set y = sum(y, (select changeY from b where a.id=b.id));
quit;

Re: simple solution sql

Kurt_Bremser — Sun, 19 Jul 2020 18:19:39 GMT

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.

Re: simple solution sql

ketpt42 — Sun, 19 Jul 2020 18:27:14 GMT

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;

Re: simple solution sql

Tom — Sun, 19 Jul 2020 19:00:23 GMT

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&docsetVersion=9.4&locale=en

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

Re: simple solution sql

makset — Mon, 20 Jul 2020 11:12:25 GMT

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

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

Re: simple solution sql

PGStats — Mon, 20 Jul 2020 15:17:02 GMT

Try

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

Re: simple solution sql

makset — Tue, 21 Jul 2020 12:59:16 GMT

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;

Thank you for your help