Adding dates in if/else statement

[Batta]

Hello SAS experts,

Not sure if something like this is possible: You can see what this code is supposed to do: it would issue “over3M” and “over6M” if time (in days) between todays day and FUP_DUE_date is over 90 and 180 days, respectively.

My question is if it possible somehow to have, instead of “over3M” and “over6M”, the date given under FUP_DUE_date for the records that have “over3M” and “over3M” while, as given, for blank fields it should stay blank (second table is what I would need to obtain)

create table Table1 as

select Subject, Max(Datepart(Date)) as Last_FUP_date format=mmddyy10.,

intnx('day', calculated Last_FUP_date, 365) as FUP_DUE_date format=mmddyy10., today()- calculated FUP_DUE_date as Date_difference,

case when (calculated Date_difference>=90) then "over3M" else "" end as Over_3M, case when (calculated Date_difference>= 180) then "over6M" else "" end as Over_6M

from mydata.InitialTable

group by Subject;

quit;

Subject	Last_FUP_date	FUP_DUE_date	Date_difference	Over_3M	Over_6M
Subject1	10/5/2019	10/4/2020	668	over3M	over6M
Subject2	3/22/2021	3/22/2022	134	over3M
Subject3	5/3/2022	5/3/2023	-273
Subject4	8/6/2021	8/6/2022	-3
Subject5	1/14/2019	1/14/2020	932	over3M	over6M
Subject6	11/18/2021	11/18/2022	-107
Subject7	12/15/2021	12/15/2022	-134
Subject8	11/6/2020	11/6/2021	270	over3M	over6M

Need to get this:

Subject	Last_FUP_date	FUP_DUE_date	Date_difference	Over_3M	Over_6M
Subject1	10/5/2019	10/4/2020	668	10/4/2020	10/4/2020
Subject2	3/22/2021	3/22/2022	134	3/22/2022
Subject3	5/3/2022	5/3/2023	-273
Subject4	8/6/2021	8/6/2022	-3
Subject5	1/14/2019	1/14/2020	932	1/14/2020	1/14/2020
Subject6	11/18/2021	11/18/2022	-107
Subject7	12/15/2021	12/15/2022	-134
Subject8	11/6/2020	11/6/2021	270	11/6/2021	11/6/2021

Thank you very much for your valuable time.

Sincerely,

Batta

[PaigeMiller]

Something like this:

case when (calculated Date_difference>=90) then FUP_DUE_date else . end as Over_3M

[Batta]

Thank you!
I've already tried that, also I tried entering calculated FUP_DUE_date as this variable is actually calculated within this step, but none of this options worked.

Thank you for trying!

[PaigeMiller]

---

@Batta wrote:
Thank you!
I've already tried that, also I tried entering calculated FUP_DUE_date as this variable is actually calculated within this step, but none of this options worked.

Thank you for trying!

---

Please don't ever tell us something didn't work and provide no other information. We can't help you when that's all the information you give us.

Show us the code. Show us the log. Show us the output. Give us information that we can use to help you.

[Batta]

My apology, I didn't post the output as I've tried similar approach before even posted this and it didn't work before as it's not working now, here is a new code based on suggestion by PaigeMiller and the output. Please let me know if I am making a mistake I am not aware of while typing:

the code:

create table Table1 as

select Subject, Max(Datepart(Date)) as Last_FUP_date format=mmddyy10.,

intnx('day', calculated Last_FUP_date, 365) as FUP_DUE_date format=mmddyy10., today()- calculated FUP_DUE_date as Date_difference,

case when (calculated Date_difference>=90) then FUP_DUE_date else . end as Over_3M,

case when (calculated Date_difference>= 180) then FUP_DUE_date else . end as Over_6M  

from mydata.InitialTable

group by Subject;

quit;

the output:

NOTE: Data file XXXX.DATA is in a format that is native to another host, or the
      file encoding does not match the session encoding. Cross Environment Data Access will be
      used, which might require additional CPU resources and might reduce performance.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: The following columns were not found in the contributing tables: FUP_DUE_date.

[Reeza]

 

create table Table1 as

select Subject, Max(Datepart(Date)) as Last_FUP_date format=mmddyy10.,

intnx('day', calculated Last_FUP_date, 365) as FUP_DUE_date format=mmddyy10., 
today()- calculated FUP_DUE_date as Date_difference,

case when (calculated Date_difference>=90) then calculated FUP_DUE_date else . end as Over_3M,

case when (calculated Date_difference>= 180) then calculated FUP_DUE_date else . end as Over_6M  

from mydata.InitialTable

group by Subject;

quit;

I think you forgot the keyword calculated in front of FUP_DUE_DATE.

Try the above and post the log if it doesn't work. 

[Batta]

Hi Reeza, I have tried that and I posted the what I got, but just to include that in this reply, here it is what I got after running the code you suggested:

Subject	Last_FUP_date	FUP_DUE_date	Date_difference	Over_3M	Over_6M
Subject1	2/5/2021	2/5/2022	180	22681	22681
Subject2	5/16/2022	5/16/2023	-285
Subject3	9/9/2021	9/9/2022	-36
Subject4	1/28/2022	1/28/2023	-177
Subject5	8/28/2019	8/27/2020	707	22154	22154
Subject6	11/6/2017	11/6/2018	1367	21494	21494

So I guess this numeric values should be converted back to dates... Not sure how to do that.

Thank you.

[PaigeMiller]

---

@Batta wrote:

My apology, I didn't post the output as I've tried similar approach before even posted this and it didn't work before as it's not working now, here is a new code based on suggestion by PaigeMiller and the output. Please let me know if I am making a mistake I am not aware of while typing:

the code:

create table Table1 as

select Subject, Max(Datepart(Date)) as Last_FUP_date format=mmddyy10.,

intnx('day', calculated Last_FUP_date, 365) as FUP_DUE_date format=mmddyy10., today()- calculated FUP_DUE_date as Date_difference,

case when (calculated Date_difference>=90) then FUP_DUE_date else . end as Over_3M,

case when (calculated Date_difference>= 180) then FUP_DUE_date else . end as Over_6M  

from mydata.InitialTable

group by Subject;

quit;

the output:

NOTE: Data file XXXX.DATA is in a format that is native to another host, or the
      file encoding does not match the session encoding. Cross Environment Data Access will be
      used, which might require additional CPU resources and might reduce performance.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: The following columns were not found in the contributing tables: FUP_DUE_date.

---

Just so there's no confusion in the future, we want the entire PROC SQL code, not the parts that you select; and the entire log for PROC SQL, not the parts that you select to show us. That's from now on, 100% of the time, no exceptions, you need to show us entire PROCs and entire log for the PROC.

[Batta]

Or if I use this code: 

proc sql;

create table Table1 as

select Subject, Max(Datepart(Date)) as Last_FUP_date format=mmddyy10.,

intnx('day', calculated Last_FUP_date, 365) as FUP_DUE_date format=mmddyy10., today()- calculated FUP_DUE_date as Date_difference,

case when (calculated Date_difference>=90) then calculated FUP_DUE_date else . end as Over_3M,

case when (calculated Date_difference>= 180) then calculated FUP_DUE_date else . end as Over_6M  

from mydata.InitialTable

group by Subject;

quit;

I would get the following: 

Subject	Last_FUP_date	FUP_DUE_date	Date_difference	Over_3M	Over_6M
Subject1	2/5/2021	2/5/2022	180	22681	22681
Subject2	5/16/2022	5/16/2023	-285
Subject3	9/9/2021	9/9/2022	-36
Subject4	1/28/2022	1/28/2023	-177
Subject5	8/28/2019	8/27/2020	707	22154	22154
Subject6	11/6/2017	11/6/2018	1367	21494	21494

Meaning that those numeric values for dates (not sure why I am obtaining them instead of dates as the original value format under the column FUP_DUE_date is date. 

Any suggestions?

Thank you very much. 

[Reeza · Accepted solution]

Formats aren't copied over, so you need to specify the format on those columns as well.

case when (calculated Date_difference>=90) then calculated FUP_DUE_date else . end as Over_3M format = ddmmyy10.,

[Batta]

THAT'S IT! 

Thank you so much Reeza,

I didn't know that the format would not get copied... 

Much appreciated!

Batta

[ballardw]

SAS dates are number of days from 1Jan1960. So apply a date format to any result that should be a date if you want something to be human readable.  Just because you set a format for the Last_FUP_date does not mean that a different variable receiving the value will use the same format.

---

@Batta wrote:

Or if I use this code: 

proc sql;

create table Table1 as

select Subject, Max(Datepart(Date)) as Last_FUP_date format=mmddyy10.,

intnx('day', calculated Last_FUP_date, 365) as FUP_DUE_date format=mmddyy10., today()- calculated FUP_DUE_date as Date_difference,

case when (calculated Date_difference>=90) then calculated FUP_DUE_date else . end as Over_3M,

case when (calculated Date_difference>= 180) then calculated FUP_DUE_date else . end as Over_6M  

from mydata.InitialTable

group by Subject;

quit;

I would get the following: 

Subject	Last_FUP_date	FUP_DUE_date	Date_difference	Over_3M	Over_6M
Subject1	2/5/2021	2/5/2022	180	22681	22681
Subject2	5/16/2022	5/16/2023	-285
Subject3	9/9/2021	9/9/2022	-36
Subject4	1/28/2022	1/28/2023	-177
Subject5	8/28/2019	8/27/2020	707	22154	22154
Subject6	11/6/2017	11/6/2018	1367	21494	21494

Meaning that those numeric values for dates (not sure why I am obtaining them instead of dates as the original value format under the column FUP_DUE_date is date. 

Any suggestions?

Thank you very much. 

---

[Batta]

Acknowledged!
Thank you.

[ballardw]

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. 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 "</>" icon or attached as text to show exactly what you have and that we can test code against.

At least describe what you mean by "none of this (sic) options worked". Did your variable have a number? Then likely need to assign a format to the value. No values? Something.