topic Re: extract month and a year from date time field in SAS Programming

extract month and a year from date time field

tparvaiz — Fri, 09 Jun 2017 18:48:32 GMT

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

Re: extract month and a year from date time field

kiranv_ — Fri, 09 Jun 2017 19:02:03 GMT

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

Re: extract month and a year from date time field

ballardw — Fri, 09 Jun 2017 19:04:11 GMT

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.

Re: extract month and a year from date time field

tparvaiz — Fri, 09 Jun 2017 19:05:23 GMT

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

thanks

Re: extract month and a year from date time field

tparvaiz — Fri, 09 Jun 2017 19:06:36 GMT

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;

Re: extract month and a year from date time field

kiranv_ — Fri, 09 Jun 2017 19:21:26 GMT

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

Re: extract month and a year from date time field

Reeza — Fri, 09 Jun 2017 19:45:11 GMT

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

Re: extract month and a year from date time field

tparvaiz — Fri, 09 Jun 2017 21:06:16 GMT

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

Re: extract month and a year from date time field

ballardw — Fri, 09 Jun 2017 21:44:06 GMT

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

Re: extract month and a year from date time field

Astounding — Fri, 09 Jun 2017 22:53:03 GMT

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

Re: extract month and a year from date time field

Reeza — Fri, 09 Jun 2017 23:21:40 GMT

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.

Re: extract month and a year from date time field

Patrick — Sat, 10 Jun 2017 01:22:56 GMT

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