topic Re: Help in Dates in SAS Programming

Help in Dates

Kalai2008 — Sun, 16 Aug 2020 16:18:30 GMT

Hello,

I am trying to use this specific filter for my program in Proc Sql , while joining tables, but I am not getting any desired output.

Select a.*

from test a

left join test1 b on a.id=b.id and

a.Trans_DT-2 <= B.Trans_DT <= A.Trans_DT

I have tried coverting Trans_dt to various formats, but getting like numbers 21998 etc

datepart(Tran_DT) format = date9. (mmddyy10., best16.) as Trans_dt

Thanks for checking !

Re: Help in Dates

Kurt_Bremser — Sun, 16 Aug 2020 16:28:21 GMT

If this code runs without ERRORs, please run PROC CONTENTS on both datasets and show us the respective lines for the Trans_DT variables. Also show us how the dates display in a viewtable window of these datasets.

Re: Help in Dates

Kalai2008 — Sun, 16 Aug 2020 16:54:15 GMT

Alphabetic List of Variables and Attributes (dataset 1)
# Variable Type Len Format
1 TRXN_EX_date Num 8 DATE9.

(Trxn_ex_date-2) as calculated_tran

Alphabetic List of Variables and Attributes dataset 2
# Variable Type Len Format
1 TRXN_EX_date Num 8 DATE9.
2 calculated_tran Num 8

Re: Help in Dates

PaigeMiller — Sun, 16 Aug 2020 17:31:24 GMT

@Kalai2008 wrote:
Alphabetic List of Variables and Attributes (dataset 1)
# Variable Type Len Format
1 TRXN_EX_date Num 8 DATE9.

(Trxn_ex_date-2) as calculated_tran

Alphabetic List of Variables and Attributes dataset 2
# Variable Type Len Format
1 TRXN_EX_date Num 8 DATE9.
2 calculated_tran Num 8

This really doesn't help, since your original code used a variable named TRANS_DT, we need to see the PROC CONTENTS on that variable in data set TEST, and PROC CONTENTS on that variable in data set TEST1; and showing us PROC CONTENTS on these other variables from other data sets helps not a bit. In fact, it would really help if you showed us a portion of the data sets test and test1.

Your code

Select  a.* from test a
left join test1 b on a.id=b.id and
a.Trans_DT-2 <= B.Trans_DT <= A.Trans_DT

doesn't depend on formats anyway. SAS always uses the unformatted values (like 21998) to compare numbers/dates/datetimes, no matter how they are formatted. So, if it does not work, you have to tell us what didn't work ... did you get the wrong output (if so, please show us and explain what is wrong)? Or did you get errors in the log (if so, please show us the entire LOG for PROC SQL)?

We need a lot more information.

Re: Help in Dates

Kurt_Bremser — Sun, 16 Aug 2020 17:45:04 GMT

In your code as posted you only used Trans_DT from both datasets. Please be consistent, and post the code as you run it, and the attributes of the variables as you use them!

Re: Help in Dates

Kalai2008 — Sun, 16 Aug 2020 17:57:20 GMT

Thank you for checking, My dataset is huge with million records and due to data privacy issues, I cant share any. But I just showed you some sample from that date. The Tran_ex_date is same as trans_dt and I changed it to date9. format.

Re: Help in Dates

Tom — Sun, 16 Aug 2020 18:43:32 GMT

You should be able to share the MIN and MAX value of some arbitrary date variable. Similarly for lines from your SAS LOG.

Remember that changing the instructions for how a variable is displayed does not in any way change the value that is stored in the variable. So whether you use DATE, MMDDYY, DDMMYY, YYMMDD or any other date type format to display the values does not matter.

If the variable has DATETIME values (number of seconds) then you would need to convert it to a DATE value (number of days) before comparing it to a date value. If it is a string that a human would thinks looks like a date value then you will need to convert it to a date value using INPUT() function with an appropriate informat for the string. If it is a number that a human might think looks like a date (such as YY,YYM,MDD or MM,DDY,YYYY) then you will need to first convert it to an actual date value by converting the number to a string using PUT() function with Z8. format and then then INPUT() function with the appropriate informat.

Re: Help in Dates

Kurt_Bremser — Sun, 16 Aug 2020 18:49:00 GMT

I am just asking for the bleeping metadata, no real values! And if you're so paranoid that you don't want to even show the variable names, then change them consistently in the code and the metadata you show us. Without having a clue, how should we help you?

Re: Help in Dates

PaigeMiller — Sun, 16 Aug 2020 18:49:20 GMT

@Kalai2008 wrote:
Thank you for checking, My dataset is huge with million records and due to data privacy issues, I cant share any. But I just showed you some sample from that date. The Tran_ex_date is same as trans_dt and I changed it to date9. format.

As stated above, we need to see the code you are running, not some made up code. If you can't show us the data, then you can make up some similar data and run that and show us the made up data. It doesn't have to be millions of records; you can make up 20 records of data, or even use real data with made up IDs. I also specifically asked you to tell us what is wrong when you run your code, instead of simply saying "I am not getting any desired output" and providing no other details.

Until we get more information from you, we probably can't help.

Re: Help in Dates

Reeza — Sun, 16 Aug 2020 22:03:58 GMT

Those variable names do not match the code shown.
Variables shown are called TRXN_EX_DATE, join code is shown using TRANS_DATE.

Show your actual code and log for starters.