BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ogarduno
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
art297
Opal | Level 21

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

 

Reeza
Super User

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. 

 

 

ogarduno
Obsidian | Level 7

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

thank you

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 4 replies
  • 1312 views
  • 2 likes
  • 4 in conversation