DATA Step, Macro, Functions and more

extract month and a year from date time field

Reply
Frequent Contributor
Posts: 129

extract month and a year from date time field

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

PROC Star
Posts: 252

Re: extract month and a year from date time field

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

Frequent Contributor
Posts: 129

Re: extract month and a year from date time field

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

 

thanks

Super User
Posts: 10,500

Re: extract month and a year from date time field

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.

Frequent Contributor
Posts: 129

Re: extract month and a year from date time field

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;

Respected Advisor
Posts: 3,892

Re: extract month and a year from date time field

[ Edited ]

@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;
PROC Star
Posts: 252

Re: extract month and a year from date time field

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

Super User
Posts: 17,829

Re: extract month and a year from date time field

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.);
Frequent Contributor
Posts: 129

Re: extract month and a year from date time field

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

Super User
Posts: 10,500

Re: extract month and a year from date time field

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. ;

Super User
Posts: 5,083

Re: extract month and a year from date time field

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.;

Super User
Posts: 17,829

Re: extract month and a year from date time field

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. 

 

 

Ask a Question
Discussion stats
  • 11 replies
  • 280 views
  • 1 like
  • 6 in conversation