Convert SAS dates into Calendar Dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Convert SAS dates into Calendar Dates

I'm looking to compare dates received for a certain variable and while importing the excel file into SAS the dates were converted into SAS dates. I'm new to this as I am an intern so I have limited knowledge on SAS. I'm looking to convert the Dates into mmddyy10 format. When trying to make the change I get the error: "You are trying to use the numeric informat DDMMYY with the character variable
Date_Received in data set DESE.DATESRECEIVED"

Can someone lead me in the right the direction?

libname DESE 'C:\Documents\DESE';
proc import out = DESE.datesreceived
datafile = 'C:\Documents\DESE\DatesReceived.xlsx'
DBMS = xlsx replace;
sheet = 'Sheet3';
getnames = yes;
run;
proc freq data = DESE.datesreceived;
format Date_Received ddmmyy10.;
table Date_Received Date;
run;


Accepted Solutions
Solution
‎07-27-2017 01:34 PM
Super User
Posts: 19,815

Re: Convert SAS dates into Calendar Dates

I would first figure out why SAS thought they were characters, dates are usually imported fine. 

 

Do you have missing values in the dates field or is there a weird format attached to the dates variables? Try formatting them as YYMMDD in Excel, then importing it into SAS to avod the issue in the first place. 

 

 

View solution in original post


All Replies
PROC Star
Posts: 7,474

Re: Convert SAS dates into Calendar Dates

You have to let us know how the dates appear when you view the sas dataset. Since it is a character variable, they were not imported as SAS dates. You can probably create the desired variable using the input function to convert the existing field to a new numeric variable which will then contain a SAS date that will accept your format. 

 

Art, CEO, AnalystFinder.com

 

Solution
‎07-27-2017 01:34 PM
Super User
Posts: 19,815

Re: Convert SAS dates into Calendar Dates

I would first figure out why SAS thought they were characters, dates are usually imported fine. 

 

Do you have missing values in the dates field or is there a weird format attached to the dates variables? Try formatting them as YYMMDD in Excel, then importing it into SAS to avod the issue in the first place. 

 

 

Occasional Contributor
Posts: 11

Re: Convert SAS dates into Calendar Dates

I had missing dates labeled "no dates" in the excel file so the entire column was read as a character.

thank you

Super User
Posts: 11,343

Re: Convert SAS dates into Calendar Dates


ogarduno wrote:

I had missing dates labeled "no dates" in the excel file so the entire column was read as a character.

thank you


Expect many such problems as you work with data that was either entered directly into Excel or sometimes just exported from another source into Excel. Cells in Excel may each have a different data type. Other data systems such as SAS and any formal database has each variable of a specific typle. Since "variable" is without additional programming logic will come from a column in Excel something has to give when the cells in the column contain different kinds of data.

 

For most recurring projects I save Excel files to CSV (which ARE not Excel files but a common text file) and write a SAS data step to read the data as needed to prevent variables from changing type from file to file.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 138 views
  • 2 likes
  • 4 in conversation