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.);
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.