extract month and a year from date time field

[tparvaiz]

Hi,

I need to extract month and a year from datetime field and here is what I have so far (which is not working)

%let date1 = '01Jan2017'd;

data temp_data;
set DB.TABLE(where = (
datepart(date_comp) > &date1)
);

format MONTH_YEAR  monyy7.;
MONTH_YEAR = datepart(date_comp);

run;

when I open the dataset, it shows month/year (e.g. JAN2017) but when I download it in excel then it changes it to the date again.

please advise

Thanks in advance

[kiranv_]

because of format monyy7. it displays so. Change it to something like yymmdd10. then it will display like date

[tparvaiz]

I want month and a year and not the date...

thanks

[ballardw]

The issue will lie somewhere in how you send the data to Excel. If you do not use something that allows use of attributes to set the EXCEL format, not SAS, then you get the default.

I tend to guess you are using Proc Export which generally has no controls over how data is sent as the appearance is not the purview of Export. Proc Export is for data values.

If you are not using export then show code on how you are sending the data to Excel to see if we can make suggestions.

[tparvaiz]

I am using proc export to save the file in xls format... something like this

PROC EXPORT DATA= Temp_data
OUTFILE= "C:\TEMP\DATA.xlsx"
DBMS=EXCEL REPLACE;
SHEET="DATA";
RUN;

[Patrick]

@tparvaiz

I believe using ODS EXCEL is your most flexible option when it comes to a need to preserve formatting in the target Excel.

proc format;
  picture dtyymon 
    other='%Y%b' (datatype=datetime)
  ;
run;
    
data Temp_data;
  format mySASDateTime dtyymon7. mySASDate yymon7.;
  mySASDateTime =datetime();
  mySASDate     =date();
  output;
run;

ods _all_ close;
ods excel
  file="c:\temp\test.xlsx"
  style=minimal
  ;
proc print noobs data=Temp_data;
run;
ods excel close;
ods listing;

[kiranv_]

Sorry @tparvaiz, it seems I have understood your question wrongly

[Reeza]

Can you export the data and use XLSX engine? Or a more recent one. 

What you're seeing is SAS has a date, but the formats are not being passed to Excel. An alternative is to convert it to a character variable using PUT and then export.

date_month_year = put(date, yymon7.);

[tparvaiz]

here is what I did

%let date1 = '01Jan2017'd;

data temp_data;
set DB.TABLE(where = (
datepart(date_comp) > &date1)
);
format MONTH_YEAR yymon7.;
MONTH_YEAR = put(date_comp, yymon7.);

run;

the column MONTH_YEAR shows nothing, it's blank

[ballardw]

Format Month_year yymon7. ; will create a numeric variable.

PUT () creates a Character variable. So the result cannot be assigned to a numeric variable.

You want

So the format woud be $7.

Format month_year $7. ;

[Astounding]

DATE_COMP is a datetime value, not a date value.  You can easily see that in your WHERE condition.  Just continue to treat it as a datetime value:

MONTH_YEAR = datepart(date_comp);

format month_year yymon7.;

[Reeza]

MONTH_YEAR = put( DATEPART(date_comp), yymon7.);

You're mixing things up, date and datetime, and character and numeric formats. 

Comment your code to avoid these types of errors.