topic Re: Modify or Update working with two tables in SAS Programming

Modify or Update working with two tables

Aleixo — Tue, 27 Mar 2018 17:50:44 GMT

Hi,

I try two search how to update or modify with two tables but the only result i found was about copy collumn(observations) from one table to another.

In my code i got it but only the observations that i update. Another option i got an update but with 10 times more observations(repeated because the comparisons).

I want is basically update a variable(PIN) in my table (Work.Base) with a string like 'Hello' in all observations. The conditions are about observation B.Date between two other dates in other table(Work.ADD).

I try it with two options, SAS and SQL. And had some errors that i don't understand.

proc sql;
update WORK.Base as B
set PIN = 'Hello' (select from WORK.ADD where (( B.Name EQ Nome) AND 
(T_new EQ B.T_final) AND
 (B.Date GE Start) AND 
(B.Date LE END));
quit;

data WORK.Base;
set WORK.ADD
modify WORK.Base;
if (( Name EQ ADD.Nome) AND (T_new EQ ADD.T_final) AND (Date GE ADD.Start) AND (Date LE ADD.END)) then 'Hello';
run;

How i can do without the statement "by" because the variables has observations very mixed and repeated.

If someone not understand i can write some examples.

Regards,

Aleixo

Re: Modify or Update working with two tables

Kurt_Bremser — Tue, 27 Mar 2018 14:36:17 GMT

This

B.Date date'n LE END

is invalid syntax, and causes further havoc down the line because of the unbalanced single quote.

Re: Modify or Update working with two tables

Aleixo — Tue, 27 Mar 2018 14:41:35 GMT

sorry my bad copy. i rectify that code. the problem is the same xD

Re: Modify or Update working with two tables

ballardw — Tue, 27 Mar 2018 14:59:58 GMT

It might help to show example data from the two data sets and what the desired result would be for that example data.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

You code contains many errors:

data WORK.Base;
   set WORK.ADDmodify WORK.Base.PIN;
   if (( B.Name EQ Nome) AND (T_new EQ B.T_final) AND (B.Date GE Start) AND (B.Date LE END)) then 'Hello';
run;

If you show the log for that data step you will have multiple errors as data step doesn't allow the B.name reference the way SQL does except for some very specific objects such as HASH. As soon as Proc SQL sees the DATA statement SAS ended the Proc SQL.

Re: Modify or Update working with two tables

Aleixo — Tue, 27 Mar 2018 15:42:23 GMT

Hi,

sorry that. i don't know why SAS code not accept enter in this forum.

Base:

Base

Name            T_new          Date              PIN
A               yellow         10Jan18
A               yellow         20Fev17
A                black         10Jan18
B                red           15Dec15
C                green         12Jan16
C                brown         15Oct17
C                orange        09Jan16
C                blue          31Apr18
A                black         26Jan18
B                red           17Mar17
E                magenta       01Jan16
F                white         10Sep17
G                gray          05Nov18

Add

Name            T_new               Start         End
A               yellow               10Jan17      11Jan18
A               yellow               11Fev17      18Fev17
A               black                10Jan17      05Nov17
B               red                  15Dec15      10Jan18
C               green                12Jan14       15Dec15
C               orange               12Jan2016    10Sep17
C               blue                 31Apr17      17Mar18
A               black                26Jan13       31Apr14
B               red                  17Mar11       20Mar12
E               magenta             17Dec15       20Mar16
G               gray                 05Nov16      15Oct17
A               yellow               10Jan15       11May15

Base (Updated)

Name            T_new          Date              PIN
A               yellow         10Jan18           Hello
A               yellow         20Fev17
A               black          10Jan18
B               red            10Dec15           Hello
C               green          12Jan16
C               brown          15Oct17
C               orange         09Jan16           Hello
C               blue           31Apr18
A               black          26Jan18
B               red            17Mar17           Hello
E               magenta        01Jan16           Hello
F               white          10Sep17
G               gray           05Nov18

Sorry the examples but it is really complex my tables. That's the main idea.

I think it is dificult to understant the tables. I think i do not wrong the update 😉

regards,

Aleixo

Re: Modify or Update working with two tables

ballardw — Tue, 27 Mar 2018 15:53:15 GMT

Copy and paste from non-text documents can be problematic. Is this supposed to be the structure for ADD:

Name T_new Start End
A yellow  10Jan17 11Jan18
A yellow  11Fev17 18Fev17
A black   10Jan17 05Nov17
B red     15Dec15 10Jan18
C green   12Jan14 15Dec15
C orange  02Jan16 10Sep17
C blue    31Apr17 17Mar18
A black   26Jan13 31Apr14
B red     17Mar11 20Mar12
E magenta 17Dec15 20Mar16
G gray    05Nov16 15Oct17
A yellow  10Jan15 11May15

Re: Modify or Update working with two tables

Aleixo — Tue, 27 Mar 2018 16:01:41 GMT

Yes. It is not perfect aligned. There are 4 columns.

The idea is a column with group -> Name

Subgroup of Name is T_new, there are reapted because happen in diferent time.

Start and End are the dates of a procedure.

Re: Modify or Update working with two tables

Kurt_Bremser — Wed, 28 Mar 2018 07:15:25 GMT

Please supply example data in a data step, like this:

data base;
input Name $ T_new $ Date :date7.;
cards;
A               yellow         10Jan18
A               yellow         20Feb17
A                black         10Jan18
B                red           15Dec15
C                green         12Jan16
C                brown         15Oct17
C                orange        09Jan16
C                blue          30Apr18
A                black         26Jan18
B                red           17Mar17
E                magenta       01Jan16
F                white         10Sep17
G                gray          05Nov18
;
run;

Test-running such a data step will reveal all typos made, like 20Fev17 or 31Apr18.

It's just a matter of basic courtesy to help the potential helpers (us) help you.

Both your updated codes:

proc sql;
update WORK.Base as B
set PIN = 'Hello' (select from WORK.ADD where (( B.Name EQ Nome) AND 
(T_new EQ B.T_final) AND
 (B.Date GE Start) AND 
(B.Date LE END));
quit;

data WORK.Base;
set WORK.ADD
modify WORK.Base;
if (( Name EQ ADD.Nome) AND (T_new EQ ADD.T_final) AND (Date GE ADD.Start) AND (Date LE ADD.END)) then 'Hello';
run;

throw obvious ERRORS that need to be fixed first.

If I read your intentions right, try this:

proc sql;
create table want as
select
  a.*,
  case
    when b.name is not missing
    then 'Hello'
    else ''
  end as PIN
from work.base a left join work.add b
on a.name = b.name and a.t_new = b.t_new and b.start <= a.date <= b.end
;
quit;

Re: Modify or Update working with two tables

Aleixo — Wed, 28 Mar 2018 14:51:20 GMT

I am very sorry about my example data. Next time i will pay attention and will do in a datastep.

Thank you so much your help. Worked fine your solution.

Regards,

Aleixo