topic Re: what's wrong with my date variables :/? in SAS Procedures

what's wrong with my date variables :/?

glee217 — Sat, 19 Aug 2017 03:35:56 GMT

*Question4;
data ResidentialSalesAuckland;
infile 'H:\stats301\A2\ResidentialSalesAuckland.dat';
input Roadname $27. Suburb $ 28-55 Townorcity $56-65 TLA $66-80 District $81-90
ListPrice 91-99 SalePrice 100-107 @108 ListDate MMDDYY9.;
run;
proc print;
title 'residential';
format ListDate date9.;
run;

This is my code. and everything before i create "ListDate" variable was fine.

But when i put this variable it gives me dot as missing value and i don't know what's the problem.

As i counted the very first example from the error, for example, 4/11/2005 is counted as 9 spaces.

This is why i put MMDDYY9. I tried to put DATE9. but it doesn't work neither.

here is the detail of error is saying:

102 *Question4;
103 data ResidentialSalesAuckland;
104 infile 'H:\stats301\A2\ResidentialSalesAuckland.dat';
105 input Roadname $27. Suburb $ 28-55 Townorcity $56-65 TLA $66-80 District $81-90
106 ListPrice 91-99 SalePrice 100-107 @108 ListDate MMDDYY9.;
107 run;

NOTE: The infile 'H:\stats301\A2\ResidentialSalesAuckland.dat' is:
Filename=H:\stats301\A2\ResidentialSalesAuckland.dat,
RECFM=V,LRECL=32767,File Size (bytes)=120390,
Last Modified=17 August 2017 10:29:46,
Create Time=17 August 2017 10:29:45

NOTE: Invalid data for ListDate in line 1 108-116.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---
1 Kealy Rd Mt Wellington Auckland City Auckland
89 355000 355000 4/11/2005 4/12/2005 4/30/2005 1 4 560 N 169
Roadname=Kealy Rd Suburb=Mt Wellington Townorcity= TLA=Auckland City District=Auckland
ListPrice=355000 SalePrice=355000 ListDate=. _ERROR_=1 _N_=1
NOTE: Invalid data for ListDate in line 2 108-116.
2 Edzell Cl Dannemora Manukau Manukau City Auckland
89 586000 2/2/2005 4/17/2005 4/30/2005 74 5 491 N 169
Roadname=Edzell Cl Suburb=Dannemora Townorcity=Manukau TLA=Manukau City District=Auckland
ListPrice=. SalePrice=586000 ListDate=. _ERROR_=1 _N_=2
NOTE: Invalid data for ListDate in line 3 108-116.
3 Te Wiata Pl Avondale - Auckland Auckland Auckland City Auckland
89 320250 2/24/2005 4/14/2005 4/30/2005 49 3 622 N 242000 2002 169
Roadname=Te Wiata Pl Suburb=Avondale - Auckland Townorcity=Auckland TLA=Auckland City
District=Auckland ListPrice=. SalePrice=320250 ListDate=. _ERROR_=1 _N_=3
NOTE: Invalid data for ListDate in line 4 108-116.
4 Delray Pl Howick Manukau City Auckland
89 645000 595000 3/8/2005 4/20/2005 5/3/2005 43 4 755 N 169
Roadname=Delray Pl Suburb=Howick Townorcity= TLA=Manukau City District=Auckland ListPrice=645000
SalePrice=595000 ListDate=. _ERROR_=1 _N_=4
NOTE: Invalid data for ListDate in line 5 108-116.
5 Carrie St Sandringham Auckland City Auckland
89 485000 3/14/2005 4/11/2005 4/30/2005 28 3 506 N 325000 2002 169
Roadname=Carrie St Suburb=Sandringham Townorcity= TLA=Auckland City District=Auckland ListPrice=.
SalePrice=485000 ListDate=. _ERROR_=1 _N_=5
NOTE: Invalid data for ListDate in line 6 108-116.
6 Christmas Rd Manurewa Manukau City Auckland
89 255000 3/15/2005 4/7/2005 5/31/2005 23 3 976 N 169
Roadname=Christmas Rd Suburb=Manurewa Townorcity= TLA=Manukau City District=Auckland ListPrice=.
SalePrice=255000 ListDate=. _ERROR_=1 _N_=6
NOTE: Invalid data for ListDate in line 7 108-116.
7 Ware Pl Pakuranga Manukau Manukau City Auckland
89 379000 342500 3/17/2005 4/16/2005 5/31/2005 30 3 683 N 230000 2002 169
Roadname=Ware Pl Suburb=Pakuranga Townorcity=Manukau TLA=Manukau City District=Auckland
ListPrice=379000 SalePrice=342500 ListDate=. _ERROR_=1 _N_=7
NOTE: Invalid data for ListDate in line 8 108-116.
8 Paunui St St Heliers Auckland City Auckland
89 1050000 1050000 3/18/2005 4/27/2005 4/30/2005 40 3 661 N 705000 169
Roadname=Paunui St Suburb=St Heliers Townorcity= TLA=Auckland City District=Auckland
ListPrice=1050000 SalePrice=1050000 ListDate=. _ERROR_=1 _N_=8
NOTE: Invalid data for ListDate in line 9 108-116.
9 Faldo Dr Howick Manukau Manukau City Auckland
89 520000 474000 3/18/2005 4/17/2005 5/31/2005 30 4 725 N 169
Roadname=Faldo Dr Suburb=Howick Townorcity=Manukau TLA=Manukau City District=Auckland
ListPrice=520000 SalePrice=474000 ListDate=. _ERROR_=1 _N_=9
NOTE: Invalid data for ListDate in line 10 108-116.
10 Maroa Rd One Tree Hill Auckland City Auckland
89 349000 3/30/2005 4/16/2005 4/30/2005 17 2 N 173000 169
Roadname=Maroa Rd Suburb=One Tree Hill Townorcity= TLA=Auckland City District=Auckland
ListPrice=. SalePrice=349000 ListDate=. _ERROR_=1 _N_=10
NOTE: Invalid data for ListDate in line 11 108-116.
11 Bannemman Rd Morningside - Auckland City Auckland City Auckland
89 400000 400000 4/2/2005 4/6/2005 4/30/2005 4 4 N 169
Roadname=Bannemman Rd Suburb=Morningside - Auckland City Townorcity= TLA=Auckland City
District=Auckland ListPrice=400000 SalePrice=400000 ListDate=. _ERROR_=1 _N_=11
NOTE: Invalid data for ListDate in line 12 108-116.
12 Baldwin Ave Mt Albert Auckland City Auckland
89 767500 767500 4/6/2005 4/7/2005 4/30/2005 1 4 696 N 169
Roadname=Baldwin Ave Suburb=Mt Albert Townorcity= TLA=Auckland City District=Auckland
ListPrice=767500 SalePrice=767500 ListDate=. _ERROR_=1 _N_=12
NOTE: Invalid data for ListDate in line 14 108-116.
14 Pukeora Ave Remuera Auckland City Auckland
89 540000 3/1/2005 4/21/2005 4/30/2005 51 2 N 169
Roadname=Pukeora Ave Suburb=Remuera Townorcity= TLA=Auckland City District=Auckland ListPrice=.
SalePrice=540000 ListDate=. _ERROR_=1 _N_=14
NOTE: Invalid data for ListDate in line 15 108-116.
15 Cardwell St Onehunga Auckland Auckland City Auckland
89 483000 3/3/2005 4/2/2005 4/30/2005 30 3 440 N 250000 2002 169
Roadname=Cardwell St Suburb=Onehunga Townorcity=Auckland TLA=Auckland City District=Auckland
ListPrice=. SalePrice=483000 ListDate=. _ERROR_=1 _N_=15
NOTE: Invalid data for ListDate in line 16 108-116.
16 Tannock Pl Mangere Manukau City Auckland
89 182000 3/23/2005 4/13/2005 4/30/2005 21 3 675 N 169
Roadname=Tannock Pl Suburb=Mangere Townorcity= TLA=Manukau City District=Auckland ListPrice=.
SalePrice=182000 ListDate=. _ERROR_=1 _N_=16
NOTE: Invalid data for ListDate in line 17 108-116.
17 Coleraine Pl Dannemora Manukau City Auckland
89 408000 408000 4/4/2005 4/4/2005 4/30/2005 0 N 169
Roadname=Coleraine Pl Suburb=Dannemora Townorcity= TLA=Manukau City District=Auckland
ListPrice=408000 SalePrice=408000 ListDate=. _ERROR_=1 _N_=17
NOTE: Invalid data for ListDate in line 18 108-116.
18 Churchill Ave Manurewa Manukau City Auckland
89 300000 305500 4/15/2005 4/22/2005 5/31/2005 7 3 971 N 270000 169
Roadname=Churchill Ave Suburb=Manurewa Townorcity= TLA=Manukau City District=Auckland
ListPrice=300000 SalePrice=305500 ListDate=. _ERROR_=1 _N_=18
NOTE: Invalid data for ListDate in line 19 108-116.
19 Walker Rd Point Chevalier Auckland Auckland City Auckland
89 470000 4/18/2005 4/26/2005 5/31/2005 8 3 0 N 320000 2002 169
Roadname=Walker Rd Suburb=Point Chevalier Townorcity=Auckland TLA=Auckland City District=Auckland
ListPrice=. SalePrice=470000 ListDate=. _ERROR_=1 _N_=19
NOTE: Invalid data for ListDate in line 20 108-116.
20 Blowers Pl Chapel Park Dannemora Manukau City Auckland
89 349000 335000 4/19/2005 4/23/2005 5/31/2005 4 3 507 N 169
Roadname=Blowers Pl Chapel Park Suburb=Dannemora Townorcity= TLA=Manukau City District=Auckland
ListPrice=349000 SalePrice=335000 ListDate=. _ERROR_=1 _N_=20
NOTE: Invalid data for ListDate in line 21 108-116.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
21 Hillcrest Gr Hillpark Manukau City Auckland
89 379000 370000 4/19/2005 4/19/2005 5/13/2005 0 3 974 N 211000 2005 169
Roadname=Hillcrest Gr Suburb=Hillpark Townorcity= TLA=Manukau City District=Auckland
ListPrice=379000 SalePrice=370000 ListDate=. _ERROR_=1 _N_=21
NOTE: 704 records were read from the infile 'H:\stats301\A2\ResidentialSalesAuckland.dat'.
The minimum record length was 169.
The maximum record length was 175.
NOTE: The data set WORK.RESIDENTIALSALESAUCKLAND has 704 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds

Re: what's wrong with my date variables :/?

PGStats — Sat, 19 Aug 2017 04:43:37 GMT

Try adding a colon and increasing the field size to 10 for dates like 11/11/2005

@108 ListDate :MMDDYY10.;

Re: what's wrong with my date variables :/?

Reeza — Sat, 19 Aug 2017 04:53:00 GMT

*Question4;
data ResidentialSalesAuckland;
infile 'H:\stats301\A2\ResidentialSalesAuckland.dat';

Informat listdate mmddyy10. ;

input Roadname $27. Suburb $ 28-55 Townorcity $56-65 TLA $66-80 District $81-90
ListPrice 91-99 SalePrice 100-107 @108 ListDate ;
run;

proc print data=ResidentialSalesAuckland;
title 'residential';
format ListDate date9.;
run;

Try using an INFORMAT. Overall the issue appears trying to read data with the wrong method. Is your file fixed format, ie each variable starts at the exact same position? Sometimes you can add a colon between the variable name and format.

Note that the format is mmddyy10 not 9. There are 9 chars if the month is less than 10, but you need to account for that case as well.

PS if you need further help it's a good idea to attach the file. Then we can test things.

@glee217 wrote:
*Question4;
data ResidentialSalesAuckland;
infile 'H:\stats301\A2\ResidentialSalesAuckland.dat';
input Roadname $27. Suburb $ 28-55 Townorcity $56-65 TLA $66-80 District $81-90
ListPrice 91-99 SalePrice 100-107 @108 ListDate MMDDYY9.;
run;
proc print;
title 'residential';
format ListDate date9.;
run;
This is my code. and everything before i create "ListDate" variable was fine.

But when i put this variable it gives me dot as missing value and i don't know what's the problem.

As i counted the very first example from the error, for example, 4/11/2005 is counted as 9 spaces.

This is why i put MMDDYY9. I tried to put DATE9. but it doesn't work neither.

here is the detail of error is saying:

102 *Question4;
103 data ResidentialSalesAuckland;
104 infile 'H:\stats301\A2\ResidentialSalesAuckland.dat';
105 input Roadname $27. Suburb $ 28-55 Townorcity $56-65 TLA $66-80 District $81-90
@106 ListPrice 91-99 SalePrice 100-107 @108 ListDate MMDDYY9.;
107 run;

NOTE: The infile 'H:\stats301\A2\ResidentialSalesAuckland.dat' is:
Filename=H:\stats301\A2\ResidentialSalesAuckland.dat,
RECFM=V,LRECL=32767,File Size (bytes)=120390,
Last Modified=17 August 2017 10:29:46,
Create Time=17 August 2017 10:29:45

NOTE: Invalid data for ListDate in line 1 108-116.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---
1 Kealy Rd Mt Wellington Auckland City Auckland
89 355000 355000 4/11/2005 4/12/2005 4/30/2005 1 4 560 N 169
Roadname=Kealy Rd Suburb=Mt Wellington Townorcity= TLA=Auckland City District=Auckland
ListPrice=355000 SalePrice=355000 ListDate=. _ERROR_=1 _N_=1
NOTE: Invalid data for ListDate in line 2 108-116.
2 Edzell Cl Dannemora Manukau Manukau City Auckland
89 586000 2/2/2005 4/17/2005 4/30/2005 74 5 491 N 169
Roadname=Edzell Cl Suburb=Dannemora Townorcity=Manukau TLA=Manukau City District=Auckland
ListPrice=. SalePrice=586000 ListDate=. _ERROR_=1 _N_=2
NOTE: Invalid data for ListDate in line 3 108-116.
3 Te Wiata Pl Avondale - Auckland Auckland Auckland City Auckland
89 320250 2/24/2005 4/14/2005 4/30/2005 49 3 622 N 242000 2002 169
Roadname=Te Wiata Pl Suburb=Avondale - Auckland Townorcity=Auckland TLA=Auckland City
District=Auckland ListPrice=. SalePrice=320250 ListDate=. _ERROR_=1 _N_=3
NOTE: Invalid data for ListDate in line 4 108-116.
4 Delray Pl Howick Manukau City Auckland
89 645000 595000 3/8/2005 4/20/2005 5/3/2005 43 4 755 N 169
Roadname=Delray Pl Suburb=Howick Townorcity= TLA=Manukau City District=Auckland ListPrice=645000
SalePrice=595000 ListDate=. _ERROR_=1 _N_=4
NOTE: Invalid data for ListDate in line 5 108-116.
5 Carrie St Sandringham Auckland City Auckland
89 485000 3/14/2005 4/11/2005 4/30/2005 28 3 506 N 325000 2002 169
Roadname=Carrie St Suburb=Sandringham Townorcity= TLA=Auckland City District=Auckland ListPrice=.
SalePrice=485000 ListDate=. _ERROR_=1 _N_=5
NOTE: Invalid data for ListDate in line 6 108-116.
6 Christmas Rd Manurewa Manukau City Auckland
89 255000 3/15/2005 4/7/2005 5/31/2005 23 3 976 N 169
Roadname=Christmas Rd Suburb=Manurewa Townorcity= TLA=Manukau City District=Auckland ListPrice=.
SalePrice=255000 ListDate=. _ERROR_=1 _N_=6
NOTE: Invalid data for ListDate in line 7 108-116.
7 Ware Pl Pakuranga Manukau Manukau City Auckland
89 379000 342500 3/17/2005 4/16/2005 5/31/2005 30 3 683 N 230000 2002 169
Roadname=Ware Pl Suburb=Pakuranga Townorcity=Manukau TLA=Manukau City District=Auckland
ListPrice=379000 SalePrice=342500 ListDate=. _ERROR_=1 _N_=7
NOTE: Invalid data for ListDate in line 8 108-116.
8 Paunui St St Heliers Auckland City Auckland
89 1050000 1050000 3/18/2005 4/27/2005 4/30/2005 40 3 661 N 705000 169
Roadname=Paunui St Suburb=St Heliers Townorcity= TLA=Auckland City District=Auckland
ListPrice=1050000 SalePrice=1050000 ListDate=. _ERROR_=1 _N_=8
NOTE: Invalid data for ListDate in line 9 108-116.
9 Faldo Dr Howick Manukau Manukau City Auckland
89 520000 474000 3/18/2005 4/17/2005 5/31/2005 30 4 725 N 169
Roadname=Faldo Dr Suburb=Howick Townorcity=Manukau TLA=Manukau City District=Auckland
ListPrice=520000 SalePrice=474000 ListDate=. _ERROR_=1 _N_=9
NOTE: Invalid data for ListDate in line 10 108-116.
10 Maroa Rd One Tree Hill Auckland City Auckland
89 349000 3/30/2005 4/16/2005 4/30/2005 17 2 N 173000 169
Roadname=Maroa Rd Suburb=One Tree Hill Townorcity= TLA=Auckland City District=Auckland
ListPrice=. SalePrice=349000 ListDate=. _ERROR_=1 _N_=10
NOTE: Invalid data for ListDate in line 11 108-116.
11 Bannemman Rd Morningside - Auckland City Auckland City Auckland
89 400000 400000 4/2/2005 4/6/2005 4/30/2005 4 4 N 169
Roadname=Bannemman Rd Suburb=Morningside - Auckland City Townorcity= TLA=Auckland City
District=Auckland ListPrice=400000 SalePrice=400000 ListDate=. _ERROR_=1 _N_=11
NOTE: Invalid data for ListDate in line 12 108-116.
12 Baldwin Ave Mt Albert Auckland City Auckland
89 767500 767500 4/6/2005 4/7/2005 4/30/2005 1 4 696 N 169
Roadname=Baldwin Ave Suburb=Mt Albert Townorcity= TLA=Auckland City District=Auckland
ListPrice=767500 SalePrice=767500 ListDate=. _ERROR_=1 _N_=12
NOTE: Invalid data for ListDate in line 14 108-116.
14 Pukeora Ave Remuera Auckland City Auckland
89 540000 3/1/2005 4/21/2005 4/30/2005 51 2 N 169
Roadname=Pukeora Ave Suburb=Remuera Townorcity= TLA=Auckland City District=Auckland ListPrice=.
SalePrice=540000 ListDate=. _ERROR_=1 _N_=14
NOTE: Invalid data for ListDate in line 15 108-116.
15 Cardwell St Onehunga Auckland Auckland City Auckland
89 483000 3/3/2005 4/2/2005 4/30/2005 30 3 440 N 250000 2002 169
Roadname=Cardwell St Suburb=Onehunga Townorcity=Auckland TLA=Auckland City District=Auckland
ListPrice=. SalePrice=483000 ListDate=. _ERROR_=1 _N_=15
NOTE: Invalid data for ListDate in line 16 108-116.
16 Tannock Pl Mangere Manukau City Auckland
89 182000 3/23/2005 4/13/2005 4/30/2005 21 3 675 N 169
Roadname=Tannock Pl Suburb=Mangere Townorcity= TLA=Manukau City District=Auckland ListPrice=.
SalePrice=182000 ListDate=. _ERROR_=1 _N_=16
NOTE: Invalid data for ListDate in line 17 108-116.
17 Coleraine Pl Dannemora Manukau City Auckland
89 408000 408000 4/4/2005 4/4/2005 4/30/2005 0 N 169
Roadname=Coleraine Pl Suburb=Dannemora Townorcity= TLA=Manukau City District=Auckland
ListPrice=408000 SalePrice=408000 ListDate=. _ERROR_=1 _N_=17
NOTE: Invalid data for ListDate in line 18 108-116.
18 Churchill Ave Manurewa Manukau City Auckland
89 300000 305500 4/15/2005 4/22/2005 5/31/2005 7 3 971 N 270000 169
Roadname=Churchill Ave Suburb=Manurewa Townorcity= TLA=Manukau City District=Auckland
ListPrice=300000 SalePrice=305500 ListDate=. _ERROR_=1 _N_=18
NOTE: Invalid data for ListDate in line 19 108-116.
19 Walker Rd Point Chevalier Auckland Auckland City Auckland
89 470000 4/18/2005 4/26/2005 5/31/2005 8 3 0 N 320000 2002 169
Roadname=Walker Rd Suburb=Point Chevalier Townorcity=Auckland TLA=Auckland City District=Auckland
ListPrice=. SalePrice=470000 ListDate=. _ERROR_=1 _N_=19
NOTE: Invalid data for ListDate in line 20 108-116.
20 Blowers Pl Chapel Park Dannemora Manukau City Auckland
89 349000 335000 4/19/2005 4/23/2005 5/31/2005 4 3 507 N 169
Roadname=Blowers Pl Chapel Park Suburb=Dannemora Townorcity= TLA=Manukau City District=Auckland
ListPrice=349000 SalePrice=335000 ListDate=. _ERROR_=1 _N_=20
NOTE: Invalid data for ListDate in line 21 108-116.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
21 Hillcrest Gr Hillpark Manukau City Auckland
89 379000 370000 4/19/2005 4/19/2005 5/13/2005 0 3 974 N 211000 2005 169
Roadname=Hillcrest Gr Suburb=Hillpark Townorcity= TLA=Manukau City District=Auckland
ListPrice=379000 SalePrice=370000 ListDate=. _ERROR_=1 _N_=21
NOTE: 704 records were read from the infile 'H:\stats301\A2\ResidentialSalesAuckland.dat'.
The minimum record length was 169.
The maximum record length was 175.
NOTE: The data set WORK.RESIDENTIALSALESAUCKLAND has 704 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds

Re: what's wrong with my date variables :/?

ballardw — Sat, 19 Aug 2017 08:33:42 GMT

Post log messages using a code box opened with the forum menu icon {i}. The positions of characters following

NOTE: Invalid data for ListDate in line 1 108-116.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---

are critical for diagnosis but the forum message windows with reformat text changing character postions. I can tell that has happended because this line in your error message:

1 Kealy Rd Mt Wellington Auckland City Auckland

appears to show Mt Wellington starting at column 12 but your input statement is reading it starting at column 28. So your error message is incomplet.

how about copying about 5 lines of data from the text file and pasting that into a code box as well.

The code you are writing for your dates is going to fail because some of your dates will exceed 9 characters if you have any thing like

10/12/2005

Placing the format on the input statement as you are doing forces SAS to read exactly that and if the value does not start in the correct column (108 from your code) AND exactly match the layout you will get errors such as you are seeing. If column 108 contains a blank then the format is actually trying to read ' 4/11/200' (count the space as character 1 and the year ends before the last digit you want) and year 200 is invalid as far as SAS is concerned as the earliest date SAS will recognize is, IIRC, the English adoption of the Gregorian calendar.

@Reeza's suggestion for an informat using mmddyy10. will read dates that only have one digit month or day of month.

DATE9. would be used to read dates that look like 12OCT2005 so there is not wonder that did not work.