DATA Step, Macro, Functions and more

IMPORT EXCEL SHEET WITH PROPER INFORMAT

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

IMPORT EXCEL SHEET WITH PROPER INFORMAT

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?


Accepted Solutions
Solution
‎10-24-2015 12:19 PM
Super User
Super User
Posts: 7,942

Re: IMPORT EXCEL SHEET WITH PROPER INFORMAT

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


All Replies
Solution
‎10-24-2015 12:19 PM
Super User
Super User
Posts: 7,942

Re: IMPORT EXCEL SHEET WITH PROPER INFORMAT

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.

New Contributor
Posts: 4

Re: IMPORT EXCEL SHEET WITH PROPER INFORMAT

Thank you,

I realy apprictiate your suggestion deptness.

 

Super User
Posts: 11,343

Re: IMPORT EXCEL SHEET WITH PROPER INFORMAT

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;

New Contributor
Posts: 4

Re: IMPORT EXCEL SHEET WITH PROPER INFORMAT

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

Thank You
Super User
Super User
Posts: 7,039

Re: IMPORT EXCEL SHEET WITH PROPER INFORMAT

[ Edited ]

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

 

 

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 579 views
  • 1 like
  • 4 in conversation