topic Re: convert date time and date to mmm yyyy in Microsoft Integration with SAS

convert date time and date to mmm yyyy

Haydn — Mon, 30 Nov 2020 04:26:56 GMT

hello,

I have two datasets that I'm using in SAS VA and also in excel and powerbi. My boss wants reports in all 3 products.

Dataset 1 has variable called "StartDate" and this is in DATETIME18. format. (From an external organistaion)

Dataset 2 has a variable called "Date" and this is DATE9. format. (From a SharePoint List)

These two variables have entries for every day of the week.

I merge these two dataset, however I'd like to create a common variable from the above and convert these to mmm yyyy (not just format). Is there a way to do this>

Re: convert date time and date to mmm yyyy

ChrisNZ — Mon, 30 Nov 2020 04:40:18 GMT

> convert these to mmm yyyy (not just format)

You mean you want a string as output?

Why not a date that you can format as you will?

> These two variables have entries for every day of the week.

How will you keep the weekly detail if you just keep monyy?

Re: convert date time and date to mmm yyyy

Haydn — Mon, 30 Nov 2020 04:48:48 GMT

How will you keep the weekly detail if you just keep monyy? (I'll still have the original variables to report at that level)
Why not a date that you can format as you will? (I'm finding that different products treat the formats differently) As an example I used the following. ReportingMonth= input(symget(StartDate),date9.);
format ReportingMonth monyy7.;
This works fine in SAS EG, but when used in PowerBI, it converts it back to dd/mm/yyy.

Re: convert date time and date to mmm yyyy

ChrisNZ — Mon, 30 Nov 2020 05:34:53 GMT

This works:

DATESTR = put(DATETIME,dtmonyy.);

Just make it a habit to browse the list of formats to find what you need.

Re: convert date time and date to mmm yyyy

SASKiwi — Mon, 30 Nov 2020 05:38:37 GMT

How are you getting your SAS dataset into PowerBI then? For reporting to work correctly in all products your date variables will have to be defined as 'proper dates', not dates stored as text strings. For Excel that means a numeric containing the number of days from 1 Jan 1900, for SAS the number of days from 1 Jan 1960 and for PowerBI it will need to be a PowerBI date (not sure how these are stored internally).

Re: convert date time and date to mmm yyyy

Kurt_Bremser — Mon, 30 Nov 2020 09:06:38 GMT

@Haydn wrote:

This works fine in SAS EG, but when used in PowerBI, it converts it back to dd/mm/yyy.

Then apply the proper format in Power BI. Storing dates as strings is, to be polite, not a bright thing to do in any environment.

Re: convert date time and date to mmm yyyy

Haydn — Mon, 30 Nov 2020 20:41:45 GMT

To be clear, I DO NOT want to store these as strings.
I wanted to create variable that where 01/07/2020 is for the Date variable, another variable is created, called ReportingMonth and this has the value of July2020, as date. I am aware I can then format as these in other products like SAS VA, Excel etc. But I was hoping avoid any sort of additional work on (such as formating etc) in other products. Especially given I have to create the same report in 3 different products. While not time consuming for one report, it does add up over time. But I guess this is not possible.

Re: convert date time and date to mmm yyyy

Tom — Mon, 30 Nov 2020 21:06:36 GMT

Do you want get a single date value for every possible day within the same month?

month=intnx('month',date,0);
format month monyy7.;

If you prefer it as a string you can skip the extra variable and just store the formatted value as the formatted value is the same no matter what day of the moth the DATE value actually has.

month=put(date,monyy7.);

If you have some values that start as datetime then you will need to convert those to dates for the first method:

month=intnx('month',datepart(datetime),0);

Or use a datetime format for the second method.

month=put(datetime,dtmonyy7.);