reading reversed date format

[Samero]

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.

[PeterClemmensen]

What date does 20200401 represent?

[Samero]

01.04.2020

[Kurt_Bremser]

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
;

[Samero]

Many thanks, can"t I do this without reentering the Datalines because it is avery long Data and it is in one Excel file, I want to import the excel file and at the same time read the date in a correct form.
eventhough i have tried what you have suggested and it didn"t work

[Kurt_Bremser · Accepted solution]

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]

wow, that worked like a magic, many thanks, really appreciate

[Samero]

only the Animalearnumber was not read correctly, it shows 2,76E+14 and they can not be converted to number format

[Tom]

---

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

[Samero]

yes it shows like this 2,76001E+14 (Local setting Europe),
when I save my Excel file as CSV format the Animalearnumber turned to be like that, I don2t know why though i tried both Standard and Number format for the column in excel befor saving it as csv

[Tom]

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]

"The possible issue is how you are looking at the text file.", yes this seems to be the issue, actualy I have notced now that all the data which I read in SAS they shows the same symbols, i think it is normal and nothing wrong with that,
appreciate your answer
Best Regards

[Kurt_Bremser]

---

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

[Reeza]

When you import your Excel file what is the type and format on the variable?
If it's numeric with a date format, you just need to change the format and can do that with a format statement. If it's a character variable then you'll have to convert it using a data step or SQL.

Option 1)
format date ddmmyyp10.;

Option 2)

data want;
set importedFromExcel;
date_want = input(date, yymmdd8.);
format date_want ddmmyyp10.;
run;

[smantha]

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