Did some looking around and did not see anything on this, building an expression in EG 7.1.
Trying to figure out how to create a new MonthYr date value variable from a time date a stamp.
MDY(MONTH(date_variable),1,YEAR(date_variable)
Have used the above code in the past when it is a mm/dd/yy stamp, the time part is throwing me off, how do I build in the DatePart? I could do it in 2 steps but I'm sure there is a way to make it 1
Use
datepart(intnx("DTMONTH", date_variable, 0))
Congratulations! I believe this is a first. You actually solved your problem in your own query!
The DATEPART function: Extracts the date from a SAS datetime value.
Tom
And I bet I can make a custom format that works directly with a datetime variable so that for many purposes no new variable is needed at all.
But I am not sure what your desired "monthyr" would look like.
Perhaps I'm missing something
/* Create_MonthYr */
(DATEPART(t1.created_at)) FORMAT=MONYY7. LABEL="Create_MonthYr" AS Create_MonthYr,
This gives me a JAN2018 format but it still has a SAS value associated with multiple days 01/01/2018, 01/02/2018, 01/03/2018 etc. this makes it more difficult to do some basic reporting like aggregating transaction counts by monthyr across other variables.
Creating a common month year SAS date value will enable this aggregation. I've done this before with MDY function when it is a date, looking for a solution for time date
@RickyS wrote:
Perhaps I'm missing something
/* Create_MonthYr */
(DATEPART(t1.created_at)) FORMAT=MONYY7. LABEL="Create_MonthYr" AS Create_MonthYr,
This gives me a JAN2018 format but it still has a SAS value associated with multiple days 01/01/2018, 01/02/2018, 01/03/2018 etc. this makes it more difficult to do some basic reporting like aggregating transaction counts by monthyr across other variables.
Creating a common month year SAS date value will enable this aggregation. I've done this before with MDY function when it is a date, looking for a solution for time date
I think you need to explain what the difficulty is with your aggregating. Almost every procedure will create groups based on the formatted value of a variable as defined in the proc.
SAS may not supply the exact datetime format you want but proc format with a Picture format will allow a great deal of customization.
data junk; do date='03OCT2017:01:23:27'dt to '15JAN2018:18:24:16'dt by 1385; value = rand('uniform'); output; end; run; proc format library=work; picture dtmonyr (default=7) low-high='%b%Y' (datatype=datetime) ; run; proc means data=junk mean max std; class date; format date dtmonyr.; var value ; run;
Did it as 2 steps
DATEPART(t1.date)
MDY(month(t1.date),1,year(t1.date))
this converts everything to 1st date of each month
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.