BookmarkSubscribeRSS Feed
tparvaiz
Obsidian | Level 7

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

11 REPLIES 11
kiranv_
Rhodochrosite | Level 12

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

tparvaiz
Obsidian | Level 7

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

 

thanks

ballardw
Super User

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
Obsidian | Level 7

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
Opal | Level 21

@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_
Rhodochrosite | Level 12

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

Reeza
Super User

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
Obsidian | Level 7

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
Super User

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
PROC Star

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
Super User
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. 

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 18152 views
  • 1 like
  • 6 in conversation