Hallo,
I have a data in which the date is reversly written
Farmnumber | Date | Animalearnumber | Diagnose |
90503537 | 20200401 | 276001304727874 | 1.10.07.10. |
90503537 | 20200401 | 276001304016422 | 1.10.07.03. |
90503537 | 20200401 | 276001304016422 | |
90503537 | 20200401 | 276001305669949 | |
90503537 | 20200401 | 276001305669949 | 1.10.07.10. |
90503537 | 20200401 | 276001304727330 | 1.10.07.06. |
90503537 | 20200401 | 276001304016088 | |
90503537 | 20200401 | 276001304016088 | |
how can I tell sas to read the date in this format and convert it to DDMMYYP10. format
Thank You in advance.
So the first thing you do is to save the data to a reasonable file format (Excel files are an utter pain in the ass to import), and then apply my data step with some slight changes.
Save the file as .csv from Excel, and then run:
data want;
infile "path_to_your_file" dlm=";" dsd truncover;
input Farmnumber :$8. Date :yymmdd8. Animalearnumber :$15. Diagnose :$15.;
format date ddmmyyp10.;
run;
DATALINES act (mostly) like any other INFILE, only that the data is written in-line into the DATA step.
What date does 20200401 represent?
See this:
data want;
infile datalines dlm=" " dsd truncover;
input Farmnumber :$8. Date :yymmdd8. Animalearnumber :$15. Diagnose :$15.;
format date ddmmyyp10.;
datalines;
90503537 20200401 276001304727874 1.10.07.10.
90503537 20200401 276001304016422 1.10.07.03.
90503537 20200401 276001304016422
90503537 20200401 276001305669949
90503537 20200401 276001305669949 1.10.07.10.
90503537 20200401 276001304727330 1.10.07.06.
90503537 20200401 276001304016088
90503537 20200401 276001304016088
;
So the first thing you do is to save the data to a reasonable file format (Excel files are an utter pain in the ass to import), and then apply my data step with some slight changes.
Save the file as .csv from Excel, and then run:
data want;
infile "path_to_your_file" dlm=";" dsd truncover;
input Farmnumber :$8. Date :yymmdd8. Animalearnumber :$15. Diagnose :$15.;
format date ddmmyyp10.;
run;
DATALINES act (mostly) like any other INFILE, only that the data is written in-line into the DATA step.
@Samero wrote:
only the Animalearnumber was not read correctly, it shows 2,76E+14 and they can not be converted to number format
Do not store that string as a NUMBER. You are not going to do arithmetic on that value. And there are limits on the number of decimal digits that can be stored. SAS uses floating point numbers so the limit is about 15 decimal digits. Just store that value as character.
If the values are not larger than the maximum contiguous integer that SAS can represent exactly then you can just change the format used to display the value.
format Animalearnumber 15. ;
Side note. Does SAS really display scientific notation using comma for decimal point instead of period? Perhaps based on your LOCALE setting? Or are you showing how the value looks in Excel or some other tool?
That is an Excel issue. First make sure the field in entered properly into the original Excel file and properly formatted in Excel. Examples:
The possible issue is how you are looking at the text file. You should open the text file with a text editor and not with Excel. If you do open the text file with Excel DO NOT let Excel just auto open it. If you want to look at it with Excel make sure you take the time to tell Excel how to read each column from the file.
@Samero wrote:
only the Animalearnumber was not read correctly, it shows 2,76E+14 and they can not be converted to number format
That's why I read it as character with a $15. informat.
You cannot tell proc import on how to read the data as proc import does it's own interpretation of the column.
Here is a mashup of all the ides given so far
1. Read excel file
2. if date is numeric in a subsequent data step:
New_date=input(put(date,$8.),yymmdd8.);
format new_date mmddyyp10.;
if date is a character
New_date=input(date,yymmdd8.);
format new_date mmddyyp10.;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: