Hi,
I have a data step where I'm converting a date/time field into just date and formatting it to monname5. ( I tried monyy5.)
in the second table I would like to group by the month but it seems like even though its a month it still has days same number if it was days.
in addition when I export it out to excel it converts the monname format back to a muddy format ...enclose is a snapshot of the script ...thanks for your assistance
DATA DeActive ;
set DeActive ;
DATE=datepart(TranDate);
FORMAT DATE monyy7.;
RUN;
proc sql;
CREATE TABLE CCMS AS
(SELECT
Atmcodenumber,
Date,
AVG(DispCashAmt) As Avg_DispCashAmt ,
AVG(DispCashCnt)As Avg_DispCashCnt ,
Avg(CurrInitAmt) As Avg_Replen,
Avg(CashAddAmt) As Avg_Cash_Add
FROM DeActive
group by DATE,Atmcodenumber
);
run;
This is pretty close. It is untested.
I don't know if GROUP BY uses the formatted value of DATE or the internal value or if you can control it.
I do know that PROC SUMMARY will use the formatted value of date by default. I prefer SUMMARY/MEANS for decriptive statistics over SQL and the code is usually shorted.
When the data get to EXCEL you will need to tell EXCEL how you want the date field formatted.
HI can you provide an example ? Thank youThank you
This is pretty close. It is untested.
DATA_NULL_,
Thank you so much for your assistance I was able to make it work...I have never used proc summary before...
If you pass a date formatted as monname to excel through ods tagsets.excelxp you can get excel to present it as a month name with the excel format "mmmm". If you use "mmm" you get the effect of sas format monname3.
peterC
Formats do not change how the value is stored. they control how the value is displayed. If you want the month name to appear in Excel, try converting it to a character value and pass the character string.
moname = put(date, monname5.);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.