This is the code that I wrote:
data libname.abc;
set libname.abc;
if num_var > 72 and date_var < '08FEB2022'd then num_var = 72;
run;
This was supposed to cap the value of num_var at 72 if it was before that specific date. However, when I open the table libname.abc on SAS EG and put numvar>72 after clicking on the where button, I see several records where the date_var has a value from years before 2022. Please note that date_var is a datetime variable.
However, when I query it as follows it doesn't return any rows:
proc sql;
create table temp_abc as
select * from libname.abc where num_var > 72 and date_var < '08FEB2022'd;
quit;
This eventually makes me think there's something wrong with the data step condition but I can't figure out what it is.
@aalluru wrote:
This is the code that I wrote:
data libname.abc; set libname.abc; if num_var > 72 and date_var < '08FEB2022'd then num_var = 72; run;
This was supposed to cap the value of num_var at 72 if it was before that specific date. However, when I open the table libname.abc on SAS EG and put numvar>72 after clicking on the where button, I see several records where the date_var has a value from years before 2022. Please note that date_var is a datetime variable.
However, when I query it as follows it doesn't return any rows:
proc sql; create table temp_abc as select * from libname.abc where num_var > 72 and date_var < '08FEB2022'd; quit;
This eventually makes me think there's something wrong with the data step condition but I can't figure out what it is.
If Date_var is actually a SAS datetime value then the underlying value used for comparisons is measured in SECONDS since 01JAN1960 :00:00:00. You are comparing it to a DATE, which is number of DAYS since 01JAN1960. So your Datetime values, unless they are pretty close to Jan 1960 are a couple of orders of magnitude greater than the date.
To compare a datetime with a date value use the DATEPART function to get something comparable:
data libname.abc; set libname.abc; if num_var > 72 and DATEPART(date_var) < '08FEB2022'd then num_var = 72; run;
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates and such.
The data and set with the same name means the source data set is completely replaced unless you have a syntax error. Depending on what you are doing that could mean loss of records or recoding values for the same record multiple times.
Can you show the log of those steps run back to back?
FYI - this type of coding is usually dangerous and can make things hard to debug/trace.
data libname.abc;
set libname.abc;
@aalluru wrote:
This is the code that I wrote:
data libname.abc; set libname.abc; if num_var > 72 and date_var < '08FEB2022'd then num_var = 72; run;
This was supposed to cap the value of num_var at 72 if it was before that specific date. However, when I open the table libname.abc on SAS EG and put numvar>72 after clicking on the where button, I see several records where the date_var has a value from years before 2022. Please note that date_var is a datetime variable.
However, when I query it as follows it doesn't return any rows:
proc sql; create table temp_abc as select * from libname.abc where num_var > 72 and date_var < '08FEB2022'd; quit;
This eventually makes me think there's something wrong with the data step condition but I can't figure out what it is.
If Date_var is actually a SAS datetime value then the underlying value used for comparisons is measured in SECONDS since 01JAN1960 :00:00:00. You are comparing it to a DATE, which is number of DAYS since 01JAN1960. So your Datetime values, unless they are pretty close to Jan 1960 are a couple of orders of magnitude greater than the date.
To compare a datetime with a date value use the DATEPART function to get something comparable:
data libname.abc; set libname.abc; if num_var > 72 and DATEPART(date_var) < '08FEB2022'd then num_var = 72; run;
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates and such.
The data and set with the same name means the source data set is completely replaced unless you have a syntax error. Depending on what you are doing that could mean loss of records or recoding values for the same record multiple times.
Unless you have datetime values from before 02JAN1960 they should all be larger than '08FEB2022'd.
Here is simple code to show what datetime value you actually compare the variable to.
1997 data _null_;
1998 date = '08FEB2022'd ;
1999 put date comma19. / date datetime19. ;
2000 datetime = '08FEB2022:00:00'dt;
2001 put datetime comma19. / datetime datetime19. ;
2002 run;
22,684
01JAN1960:06:18:04
1,959,897,600
08FEB2022:00:00:00
So 08EB2022 is over 22 thousand days after the start of 1960. But that number of seconds is only a little over 6 hours after the start of 1960.
To get a number in the right ball park use a datetime literal instead.
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.