Modify or Update working with two tables

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Modify or Update working with two tables

[ Edited ]

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

 


Accepted Solutions
Solution
‎03-28-2018 10:51 AM
Super User
Posts: 10,209

Re: Modify or Update working with two tables

[ Edited ]

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,209

Re: Modify or Update working with two tables

This

B.Date date'n LE END

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 69

Re: Modify or Update working with two tables

Posted in reply to KurtBremser

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

Super User
Posts: 13,498

Re: Modify or Update working with two tables

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-dat... 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.

 

Contributor
Posts: 69

Re: Modify or Update working with two tables

[ Edited ]

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 Smiley Wink

 

regards,

Aleixo

Super User
Posts: 13,498

Re: Modify or Update working with two tables

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
Contributor
Posts: 69

Re: Modify or Update working with two tables

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.

Solution
‎03-28-2018 10:51 AM
Super User
Posts: 10,209

Re: Modify or Update working with two tables

[ Edited ]

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 69

Re: Modify or Update working with two tables

Posted in reply to KurtBremser

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 133 views
  • 0 likes
  • 3 in conversation