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>
This works:
DATESTR = put(DATETIME,dtmonyy.);
Just make it a habit to browse the list of formats to find what you need.
> 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?
This works:
DATESTR = put(DATETIME,dtmonyy.);
Just make it a habit to browse the list of formats to find what you need.
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).
@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.
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.);
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!