topic Re: Cannot convert dates in SAS Programming

Cannot convert dates

FrustratedBio — Thu, 25 Feb 2021 05:32:24 GMT

So here is my code. I'm following what my professors says to a T, but for some reason, I cannot convert date. It just wont work. The raw format is 12DEC2000 or Date9, in im correct. For some reason, the code I have below won't let me convert it.

data work.sanfran;
Infile '/folders/myfolders/sasuser.v94/Data for Classes 3 to 6/sfosch.dat';
Input @1 FlightID $7.
@8 RouteID $7.
@15 Destination $6.
@31 Model $6.
@40 Date $date9.
@64 TotPassCap 4.;

run;

proc print data=work.sanfran;
Format date $ddmmyy10.; /* THIS SHOULD BE WORKING*/
run;

Re: Cannot convert dates

SASKiwi — Thu, 25 Feb 2021 05:55:42 GMT

SAS dates are numeric variables and so require numeric INFORMATs and FORMATs You need to remove the dollar signs preceding your date formats:

data work.sanfran;
Infile '/folders/myfolders/sasuser.v94/Data for Classes 3 to 6/sfosch.dat';
Input @1 FlightID $7.
@8 RouteID $7.
@15 Destination $6.
@31 Model $6.
@40 Date date9.
@64 TotPassCap 4.;

run;

proc print data=work.sanfran;
Format date ddmmyy10.; /* THIS SHOULD BE WORKING*/
run;

Re: Cannot convert dates

Kurt_Bremser — Thu, 25 Feb 2021 08:53:09 GMT

Format date $ddmmyy10.; /* THIS SHOULD BE WORKING*/

This should positively NOT work, as there is no $DDMMYY format.

The existing format DDMMYY is for numeric values.

Similarly, there is no $DATE informat, which a quick look at your log will reveal:

data test;
input date $date9.;
datalines;
01jan1960
;

Log:

 73         data test;
 74         input date $date9.;
                       _______
                       485
 NOTE 485-185: Informat $DATE was not found or could not be loaded.
 
 75         datalines;

This, OTOH, will work:

data test;
input date date9.;
datalines;
01jan1960
;

proc print data=test;
format date ddmmyy10.;
run;

Log:

 73         data test;
 74         input date date9.;
 75         datalines;
 
 NOTE: The data set WORK.TEST has 1 observations and 1 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 77         ;
 78         
 79         proc print data=test;
 80         format date ddmmyy10.;
 81         run;
 
 NOTE: There were 1 observations read from the data set WORK.TEST.
 NOTE:  Verwendet wurde: PROZEDUR PRINT - (Gesamtverarbeitungszeit):
       real time           0.03 seconds
       cpu time            0.02 seconds

DATE is now read as a numeric SAS date value, and can be displayed with one of the many date-related numeric formats.

I recommend this:

About SAS Date, Time, and Datetime Values

for further reading.

Re: Cannot convert dates

FrustratedBio — Thu, 25 Feb 2021 14:35:32 GMT

Thanks a bunch for your reply. The problem is that it doesn't seem to want to do that for whatever reason. If I remove my dollar signs I get a warning message and an empty column in my results.

Re: Cannot convert dates

Kurt_Bremser — Thu, 25 Feb 2021 15:14:57 GMT

Please post the log from your data step. Include all the code, and if you get lots of "invalid data" messages, just a few of them will be enough.

Please use the indicated button to post the log text:

Re: Cannot convert dates

SASKiwi — Thu, 25 Feb 2021 19:16:03 GMT

In that case you may need to adjust your INPUT statement because the date is probably being read from the wrong columns. Please post your complete SAS log as @Kurt_Bremser has asked.

Re: Cannot convert dates

FrustratedBio — Thu, 25 Feb 2021 23:59:31 GMT

OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 data work.sanfran;

74 Infile '/folders/myfolders/sasuser.v94/Data for Classes 3 to 6/sfosch.dat';

75 Input @1 FlightID $7.

76 @8 RouteID $7.

77 @15 Destination $6.

78 @31 Model $6.

79 @40 Date date9.

80 @64 TotPassCap 4.;

82 run;

NOTE: The infile '/folders/myfolders/sasuser.v94/Data for Classes 3 to 6/sfosch.dat' is:

Filename=/folders/myfolders/sasuser.v94/Data for Classes 3 to 6/sfosch.dat,

Owner Name=root,Group Name=vboxsf,

Access Permission=-rwxrwx---,

Last Modified=24May2010:17:54:24,

File Size (bytes)=4212

NOTE: Invalid data for Date in line 1 40-48.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

1 IA112000000112 HNDJetCruise LF8100 01DEC2000 6 19 31 171 255 61300 79077 79

FlightID=IA11200 RouteID=0000112 Destination=HND Model=LF8100 Date=. TotPassCap=255 _ERROR_=1 _N_=1

NOTE: Invalid data for Date in line 2 40-48.

2 IA018040000018 SEAJetCruise SF1000 01DEC2000 6 10 123 150 10300 13287 79

FlightID=IA01804 RouteID=0000018 Destination=SEA Model=SF1000 Date=. TotPassCap=150 _ERROR_=1 _N_=2

NOTE: Invalid data for Date in line 3 40-48.

3 IA029010000029 HNLJetCruise LF5200 02DEC2000 7 13 24 138 207 47400 61146 79

FlightID=IA02901 RouteID=0000029 Destination=HNL Model=LF5200 Date=. TotPassCap=207 _ERROR_=1 _N_=3

NOTE: Invalid data for Date in line 4 40-48.

4 IA031000000031 ANCJetCruise LF8100 02DEC2000 7 13 22 250 255 24800 31992 79

FlightID=IA03100 RouteID=0000031 Destination=ANC Model=LF8100 Date=. TotPassCap=255 _ERROR_=1 _N_=4

NOTE: Invalid data for Date in line 5 40-48.

5 IA029010000029 HNLJetCruise LF5200 03DEC2000 1 14 25 132 207 48200 62178 79

FlightID=IA02901 RouteID=0000029 Destination=HNL Model=LF5200 Date=. TotPassCap=207 _ERROR_=1 _N_=5

NOTE: Invalid data for Date in line 6 40-48.

6 IA031000000031 ANCJetCruise MF4000 03DEC2000 1 16 243 267 25600 33024 79

FlightID=IA03100 RouteID=0000031 Destination=ANC Model=MF4000 Date=. TotPassCap=267 _ERROR_=1 _N_=6

NOTE: Invalid data for Date in line 7 40-48.

7 IA008000000008 RDUJetCruise MF4000 04DEC2000 2 16 243 267 25600 33024 79

FlightID=IA00800 RouteID=0000008 Destination=RDU Model=MF4000 Date=. TotPassCap=267 _ERROR_=1 _N_=7

NOTE: Invalid data for Date in line 8 40-48.

8 IA018050000018 SEAJetCruise SF1000 04DEC2000 2 11 123 150 10100 13029 79

FlightID=IA01805 RouteID=0000018 Destination=SEA Model=SF1000 Date=. TotPassCap=150 _ERROR_=1 _N_=8

NOTE: Invalid data for Date in line 9 40-48.

9 IA018040000018 SEAJetCruise LF5100 06DEC2000 4 11 12 111 165 12500 16125 79

FlightID=IA01804 RouteID=0000018 Destination=SEA Model=LF5100 Date=. TotPassCap=165 _ERROR_=1 _N_=9

NOTE: Invalid data for Date in line 10 40-48.

10 IA031010000031 ANCJetCruise LF8100 06DEC2000 4 14 26 233 255 28000 36120 79

FlightID=IA03101 RouteID=0000031 Destination=ANC Model=LF8100 Date=. TotPassCap=255 _ERROR_=1 _N_=10

NOTE: Invalid data for Date in line 11 40-48.

11 IA018020000018 SEAJetCruise SF1000 07DEC2000 5 10 N/A 132 150 8500 10965 79

FlightID=IA01802 RouteID=0000018 Destination=SEA Model=SF1000 Date=. TotPassCap=150 _ERROR_=1 _N_=11

NOTE: Invalid data for Date in line 12 40-48.

12 IA112000000112 HNDJetCruise LF8100 08DEC2000 6 17 33 194 255 56700 73143 79

FlightID=IA11200 RouteID=0000112 Destination=HND Model=LF8100 Date=. TotPassCap=255 _ERROR_=1 _N_=12

NOTE: Invalid data for Date in line 13 40-48.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

13 IA031010000031 ANCJetCruise LF8100 08DEC2000 6 13 17 242 255 26400 34056 79

FlightID=IA03101 RouteID=0000031 Destination=ANC Model=LF8100 Date=. TotPassCap=255 _ERROR_=1 _N_=13

NOTE: Invalid data for Date in line 14 40-48.

14 IA018040000018 SEAJetCruise SF1000 08DEC2000 6 12 119 150 10700 13803 79

FlightID=IA01804 RouteID=0000018 Destination=SEA Model=SF1000 Date=. TotPassCap=150 _ERROR_=1 _N_=14

NOTE: Invalid data for Date in line 15 40-48.

15 IA112010000112 HNDJetCruise LF8100 09DEC2000 7 15 32 175 255 61100 78819 79

FlightID=IA11201 RouteID=0000112 Destination=HND Model=LF8100 Date=. TotPassCap=255 _ERROR_=1 _N_=15

NOTE: Invalid data for Date in line 16 40-48.

16 IA031000000031 ANCJetCruise MF4000 09DEC2000 7 14 237 267 27200 35088 79

FlightID=IA03100 RouteID=0000031 Destination=ANC Model=MF4000 Date=. TotPassCap=267 _ERROR_=1 _N_=16

NOTE: Invalid data for Date in line 17 40-48.

17 IA018050000018 SEAJetCruise SF1000 10DEC2000 1 12 126 150 9300 11997 79

FlightID=IA01805 RouteID=0000018 Destination=SEA Model=SF1000 Date=. TotPassCap=150 _ERROR_=1 _N_=17

NOTE: Invalid data for Date in line 18 40-48.

18 IA018030000018 SEAJetCruise SF1000 11DEC2000 2 12 136 150 7300 9417 79

FlightID=IA01803 RouteID=0000018 Destination=SEA Model=SF1000 Date=. TotPassCap=150 _ERROR_=1 _N_=18

NOTE: Invalid data for Date in line 19 40-48.

19 IA112010000112 HNDJetCruise MF2100 12DEC2000 3 18 31 178 237 60100 77529 79

FlightID=IA11201 RouteID=0000112 Destination=HND Model=MF2100 Date=. TotPassCap=237 _ERROR_=1 _N_=19

NOTE: Invalid data for Date in line 20 40-48.

WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

20 IA112000000112 HNDJetCruise LF8100 13DEC2000 4 17 29 179 255 60500 78045 79

FlightID=IA11200 RouteID=0000112 Destination=HND Model=LF8100 Date=. TotPassCap=255 _ERROR_=1 _N_=20

NOTE: 52 records were read from the infile '/folders/myfolders/sasuser.v94/Data for Classes 3 to 6/sfosch.dat'.

The minimum record length was 79.

The maximum record length was 79.

NOTE: The data set WORK.SANFRAN has 52 observations and 6 variables.

NOTE: DATA statement used (Total process time):

real time 0.05 seconds

cpu time 0.01 seconds

84 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Here is the log

Re: Cannot convert dates

Kurt_Bremser — Fri, 26 Feb 2021 10:15:50 GMT

Please do ALWAYS

(as in ALWAYS)

use the indicated button for posting logs, otherwise the horizontal formatting is destroyed and the log becomes mostly useless.

Similarly, you can open the file with a text editor (like Windows Editor or Notepad++, NOT a word processor like MS Word) and copy/paste a few lines into a window opened with the same button. Or you change the filename extension to .txt and attach the file to your next post.

I suspect that the positions in the code do not match the positions in the file. But I can only tell for sure once I see the data as it is.