topic Re: How to cap the value of existing column based on date in SAS Programming

How to cap the value of existing column based on date

aalluru — Fri, 05 Aug 2022 21:04:40 GMT

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.

Re: How to cap the value of existing column based on date

Reeza — Fri, 05 Aug 2022 21:10:49 GMT

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;

Re: How to cap the value of existing column based on date

ballardw — Fri, 05 Aug 2022 21:34:29 GMT

@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/ta-p/424354 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.

Re: How to cap the value of existing column based on date

Tom — Fri, 05 Aug 2022 23:46:59 GMT

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.