Hi Rohan
Yes. I know of this problem, I used to always use the character conversion as well. However fairly recently I had to start using actual dates. Last I checked, this has not been overcome in PowerBI yet. There is great to get a proper connector between SAS and PowerBI, but I suspect there is some corporate disagreement or something. This problem is also prevalent if you connect Excel directly to the SAS files. I can't remember if you use SAS to store data on a SQL server Db and then connect Excel/PowerBi to that, if this problem also exists - its been a while since I could do that, so I can't recall.
So what I do is one of two things,
1. After you final data step (or what ever it may be) convert all you date column to excel by subtracting 21916 (that is the number of days difference in their reference date).
e.g. Loan_application_date = Loan_application_date - 21916;
2. Create a new date variable.
e.g. Loan_application_date_Excel = Loan_application_date -21916;
However, I do believe that if you export your files to an excel file, it does the conversion for you - but I am not sure off the top of my head. Also, if you use the SAS plugin, then it also does the conversion for you. I however don't use the plugin to directly connect to data as it does not link in/hookup to the 'refresh all' function of Excel.
Should you want to make the time conversion - as SAS works in seconds after midnight, while Excel works in fractions of a day. This can be achieved in a similar means:
1. After you final data step (or what ever it may be) convert all you date column to excel by dividing 86400 (that is the number of second in a day).
e.g. Loan_application_time = Loan_application_time / 86400;
2. Create a new date variable.
e.g. Loan_application_time_Excel = Loan_application_time / 86400;
Should you want to make the datetime conversion.
This can be achieved in a similar means:
1. After you final data step (or what ever it may be) convert all you date column to excel by dividing 86400 (that is the number of second in a day).
e.g. Loan_application_time = (Loan_application_time / 86400) - 21916;
2. Create a new date variable.
e.g. Loan_application_time_Excel = (Loan_application_time / 86400) - 21916;
then obviously, you can do the reverse as well if you need to take Excel date/time/datetime to SAS.
Also, FYI SAS & SQL have the same reference date and time units.
Hope this helps
~GD
... View more
