BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aleixo
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

8 REPLIES 8
Aleixo
Quartz | Level 8

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

ballardw
Super User

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.

 

Aleixo
Quartz | Level 8

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

ballardw
Super User

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
Aleixo
Quartz | Level 8

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.

Kurt_Bremser
Super User

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;

Aleixo
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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