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
Is it a requirement to use SQL?
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!!!
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 .
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.
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;
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.