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
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;
This
B.Date date'n LE END
is invalid syntax, and causes further havoc down the line because of the unbalanced single quote.
sorry my bad copy. i rectify that code. the problem is the same xD
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.
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
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
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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.