PROC SQL UPDATE Value

[Denana]

Hello everyone. I tried to Update my Values in my Column "DATE of Operation" . Every Date happend before the Diagnoses I want to delete and have an empty Field there, but stll have my ID Number, but just ONCE for the Case 4 and 5.

I used the Code :

proc sql;
UPDATE TABLE_1
SET Date_of_Operation=Date of Operation*

case when Date_of_Operation > Date of Diagnosis then Date of Operation= .
end;

ID	Date of Diagnosis	Date of Operation
1	02.01.2017	02.01.2017
2	05.09.2015	07.09.2015
3	06.02.2019	09.03.2019
4	08.09.2014	01.01.2014
4	08.09.2014	05.01.2014
5	06.11.2020	01.01.2020
5	06.11.2020	01.02.2020

So i want my export like this :

ID	Date of Diagnosis	Date of Operation
1	02.01.2017	02.01.2017
2	05.09.2015	07.09.2015
3	06.02.2019	09.03.2019
4	08.09.2014
5	06.11.2020

My Code is not working. Can someone Help me ?

Thanks

[PeterClemmensen]

Is it a requirement to use SQL?

[Denana]

First thank you for this Code, it is working. But now I have one more Problem. My Table is looking like this:

ID	TUMOR_ID	Date of Diagnosis	OP Date
1	1	02.01.2017	02.01.2017
2	1	05.09.2015	07.09.2015
3	1	06.02.2019	09.03.2019
4	1	08.09.2014	08.09.2014
4	2	25.10.2015	30.10.2015
5	1	06.11.2020	01.01.2020
5	1	06.11.2020	01.02.2020

and I want the new Table like this: 

ID	TUMOR_ID	Date of Diagnosis	OP Date
1	1	02.01.2017	02.01.2017
2	1	05.09.2015	07.09.2015
3	1	06.02.2019	09.03.2019
4	1	08.09.2014	08.09.2014
4	2	25.10.2015	30.10.2015
5	1	06.11.2020

With the Code before, I become Deleted also the ID Numer 4 , so it reduce me my Table. I want for the ID Number both Tumor_IDs. 

Thanks for you Help!!! 

[Denana]

Schould I just change in the CODE do until(last.TUMOR_ID) ?

data want(drop = flag);

do until(last.TUMOR_ID); THIS????
set have;
by ID;
if DateofOperation < DateofDiagnosis then flag = 1;
end;

if flag then DateofOperation = .;

run;

[PeterClemmensen]

You could use a data step like this

data have;
input ID (DateofDiagnosis DateofOperation)(:ddmmyy10.);
format Date: ddmmyyp10.;
datalines;
1 02.01.2017 02.01.2017 
2 05.09.2015 07.09.2015 
3 06.02.2019 09.03.2019 
4 08.09.2014 01.01.2014 
4 08.09.2014 05.01.2014 
5 06.11.2020 01.01.2020 
5 06.11.2020 01.02.2020 
;

data want(drop = flag);

   do until(last.ID);
      set have;
      by ID;
      if DateofOperation < DateofDiagnosis then flag = 1;
   end;

   if flag then DateofOperation = .;

run;

Result:

ID DateofDiagnosis DateofOperation
1  02.01.2017      02.01.2017
2  05.09.2015      07.09.2015
3  06.02.2019      09.03.2019
4  08.09.2014      .
5  06.11.2020      .

[Tom]

Modifying datasets in place is not a normal way to use SAS.   Unless your data is extremely large you will find it much easier to create new datasets instead.

data have ;
  input id diagdate :yymmdd. operdate :yymmdd. ;
  format diagdate operdate yymmdd10.;
cards;
1 2017-02-01 2017-02-01
2 2015-05-09 2015-07-09
3 2019-06-02 2019-09-03
4 2014-08-09 2014-01-01
4 2014-08-09 2014-05-01
5 2020-06-11 2020-01-01
5 2020-06-11 2020-01-02
;

data expect ;
  input id diagdate :yymmdd. operdate :yymmdd. ;
  format diagdate operdate yymmdd10.;
cards;
1 2017-02-01 2017-02-01
2 2015-05-09 2015-07-09
3 2019-06-02 2019-09-03
4 2014-08-09 .
5 2020-06-11 .
;

proc sql;
create table want as 
  select distinct 
       id
     , diagdate
     , case when operdate < diagdate then . else operdate end as operdate format=yymmdd10.
  from have
  order by 1,2,3
;
quit;

proc compare data=want compare=expect;
run;
       

If you actually need to support a highly volatile dataset then perhaps you should host that data in a database system instead.

[Ksharp]

data have;
input ID (DateofDiagnosis DateofOperation)(:ddmmyy10.);
format Date: ddmmyyp10.;
datalines;
1 02.01.2017 02.01.2017 
2 05.09.2015 07.09.2015 
3 06.02.2019 09.03.2019 
4 08.09.2014 01.01.2014 
4 08.09.2014 05.01.2014 
5 06.11.2020 01.01.2020 
5 06.11.2020 01.02.2020 
;
proc sql;
create table want as
select ID,DateofDiagnosis,
ifn(sum(DateofOperation<DateofDiagnosis),.,max(DateofOperation)) as DateofOperation format=ddmmyyp10.
 from have
  group by ID,DateofDiagnosis;
quit;