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
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.,
Something like this:
case when (calculated Date_difference>=90) then FUP_DUE_date else . end as Over_3M
@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.
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;
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.
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.
@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.
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.
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.,
THAT'S IT!
Thank you so much Reeza,
I didn't know that the format would not get copied...
Much appreciated!
Batta
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.
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.
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.