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

To import below data used

RollNoNameDateMarksRemarks
1Jai24/10/199074Good
2Ram16/11/200184Better
3Rose9/2/201354Bad
4Pop12/7/200196Best

This program is used to IMPORT

PROC IMPORT OUT=Test1
DATAFILE="C:xyz\Record.xls"
DBMS=Excel REPLACE;
SHEET="Sheet1$";
GETNAMES=Yes;
MIXED=NO;
USEDATE=YES;
SCANTIME=Yes;
RUN;

 

OUTPUT:

Obs RollNo Name_ Date Marks_ Remarks 1 2 3 4

1Jai.74Good
2Ram.84Better
3Rose02/09/201354Bad
4Pop07/12/200196Best

 

Here I'm not able to read date in proper format can anyone help me?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, the simple answer is don't use Excel.  This may sound harsh, however you have to remember that Excel was not designed as a data capture, database, data transfer format etc.  You can see that the first two records, something in the cell of those two values, and I cant tell from here, does not conform to a strcutuctured database like format which is required by SAS.  This is because Excel is unstrcutured.  You will find many examples of this if you continue to use a tool for something it was not intended.  Secondly proc import is a "guessing" procedure.   It looks at the file and attempts to do a best fit based on what it sees in the first few rows.  Again, this is far from optimal, its your data, you should be telling the computer what to do, not letting it guess.  

So, I would suggest you do this:

1) Go back to the source of the data and instruct them to provide a proper data transfer file format, for example XML, CSV, and recommeded would be to setup a data transfer agreement which details the structure of the data.  That way everything is clear and agree upon up front and will mean you take less responsibility in converting or interpreting data and only have to program your code once as changes would not be in the agreement.  

Now the above I understand is sometimes difficult, and would be the ideal scenario, up to you how much you implement however I would say that once you have worked with an agreement in place, everything else just feels like a dog chasing its own tail.  If they cannot provide a proper data transfer format, and in this day and age they should not be in the role if they can't provide alternatives, then you will have to take the responsibility yourself.  Conversion from Excel to any format has many pitfalls, from text in numeric variables, special characters, mixed formats, etc.  It could be as simple as SaveAs-> File type CSV, however you need to check no data gets messed up.

 

2) Secondly, write a proper datastep import for the CSV data, prefereable based upon the agreed transfer agreement, this way any differences in the data you can go back to the source with.  Its very simple, and if your really lazy you can get proc import to create most of the code for you (look at the log and copy it from there).  In this datastep you would read the file and specify length, name, label, informat etc. explicitly so there is no guessing.

 

Then once QC'd your data is ready to go.  This question tends to come up once a week at least and the problem is the use of Excel.

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, the simple answer is don't use Excel.  This may sound harsh, however you have to remember that Excel was not designed as a data capture, database, data transfer format etc.  You can see that the first two records, something in the cell of those two values, and I cant tell from here, does not conform to a strcutuctured database like format which is required by SAS.  This is because Excel is unstrcutured.  You will find many examples of this if you continue to use a tool for something it was not intended.  Secondly proc import is a "guessing" procedure.   It looks at the file and attempts to do a best fit based on what it sees in the first few rows.  Again, this is far from optimal, its your data, you should be telling the computer what to do, not letting it guess.  

So, I would suggest you do this:

1) Go back to the source of the data and instruct them to provide a proper data transfer file format, for example XML, CSV, and recommeded would be to setup a data transfer agreement which details the structure of the data.  That way everything is clear and agree upon up front and will mean you take less responsibility in converting or interpreting data and only have to program your code once as changes would not be in the agreement.  

Now the above I understand is sometimes difficult, and would be the ideal scenario, up to you how much you implement however I would say that once you have worked with an agreement in place, everything else just feels like a dog chasing its own tail.  If they cannot provide a proper data transfer format, and in this day and age they should not be in the role if they can't provide alternatives, then you will have to take the responsibility yourself.  Conversion from Excel to any format has many pitfalls, from text in numeric variables, special characters, mixed formats, etc.  It could be as simple as SaveAs-> File type CSV, however you need to check no data gets messed up.

 

2) Secondly, write a proper datastep import for the CSV data, prefereable based upon the agreed transfer agreement, this way any differences in the data you can go back to the source with.  Its very simple, and if your really lazy you can get proc import to create most of the code for you (look at the log and copy it from there).  In this datastep you would read the file and specify length, name, label, informat etc. explicitly so there is no guessing.

 

Then once QC'd your data is ready to go.  This question tends to come up once a week at least and the problem is the use of Excel.

Kumar1
Calcite | Level 5

Thank you,

I realy apprictiate your suggestion deptness.

 

ballardw
Super User

Are all your dates in day month year order? You may need to set the system option datestyle to DMY.

Run

proc options group=languagecontrol;run;

 

and look for the datestyle setting. It likely is MDY which will expects dates to start with the month and will not accept a month of 24.

Change to DMY by

Options datestyle=DMY;

Kumar1
Calcite | Level 5
Hi Ballardw,
Scenario is with many different types of date's so it might help me in any other.

Thank You
Tom
Super User Tom
Super User

One way to prevent these issues with month first or day first date formats is to always use year, month, day format when transferring date values.  Then there is no confusing between differnet regions of the world using different conventions.  They also have the advantage that when you sort the text version of the date the result is proper chronological order.

 

data want ;
  input RollNo Name :$10. Date :yymmdd10. Marks Remarks :$20. ;
  format date yymmdd10. ;
cards;
1 Jai  1990/10/24 74 Good
2 Ram 2001/11/06 84 Better
3 Rose 2013/02/09 54 Bad
4 Pop 2001/07/21 96 Best
;;;;

 

 

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
  • 5 replies
  • 4725 views
  • 1 like
  • 4 in conversation