Date Format

[anilgvdbm]

Dear All,

My name is Anil i getting error in data format while extracting data from text format.

error is like this

codes:

Date_of_Survey 60 -65
Date_of_Despatch 66 -71
Time_to_canvass 72 -74;
format Date_of_Survey ddmmyy10. Date_of_Despatch ddmmyy10. ;
;run;

Please how to change the codes to get proper date. 

here i'm attaching sample data also.

000244427003311012090901120111120110100000 421 3890 05031313031312022222 1 2 1554800
000244427003311012090901120111130110100000 421 3890 07031307031311022222 1 2 33800
000244427003311012090901120111220110100000 221 3890 06031313031312022222 1 2 2636400
000244427003311012090901120111230110100000 111 3890 07031313031312022222 1 2 84500
000244427003311012090901120111230210100000 121 3890 09031309031311021222 1 2 84500
000244427003311012090901120111230310100000 421 3690 06031313031312022222 1 2 84500
000244427003311012090901120111230410100000 111 3890 08031313031312022212 1 2 84500
000244427003311012090901120111240110100000 421 3890 05031313031312022222 1 2 67600
000244417003311012090902110111120110100000 111 4035 090413150413 9022222 1 2 15300
000244417003311012090902110111120210100000 111 4035 100413150413 9522222 1 2 15300
000244417003311012090902110111120310100000 211 4035 100413150413 8522222 1 2 15300
000244417003311012090902110111120410100000 111 4035 070413150413 9022222 1 2 15300
000244417003311012090902110111220110100000 421 4035 070413150413 7522222 1 2 411400
000244417003311012090902110111220210100000 221 4035 070413150413 9022222 1 2 411400
000244417003311012090902110111220310100000 2129 4035 080413150413 9022222 1 2 411400
000244417003311012090902110111220410100000 411 4035 080413150413 8022222 1 2 411400
000244457003311012101001120111120110100000 311 4035 14021318021315022222 1 2 1209800
0002444570033110121010011201111301101000009921 4035 130213180213 6022212 1 2 43833
000244457003311012101001120111130210100000 411 4035 12021318021315022222 1 2 43833
000244457003311012101001120111130310100000 111 4035 11021318021315022221 1 2 43833
000244457003311012101001120111220110100000 111 4035 11021318021315022222 1 2 2498500
000244457003311012101001120111240110100000 111 4035 17021318021312022212 1 2 131500
000244457003311012101001120111240210100000 411 4035 08021318021313522222 1 2 131500
000244457003311012101001120111240310100000 111 4035 11021318031316022222 1 2 131500
000244407003311012101002110111120110100000 121 3890 140113190213 8522222 1 2 332000
000244407003311012101002110003130110100000 221 3890 150113190213 9522222 1 2 24900
000244407003311012101002110111220110100000 221 3890 21011319021312022222 1 2 4233000
000244407003311012101002110111230110100000 121 3890 22011319021310522222 1 2 435750
000244407003311012101002110111230210100000 111 3890 23011319021310022222 1 2 435750
000244407003311012101002110111240110100000 221 3890 24011319021312022122 1 2 124500
000244407003311012101002110111240210100000 111 3890 28011319021310522222 1 2 124500
000244407003311012101002110111240310100000 121 3890 28011319021312022222 1 2 124500
000244497003311012111101120110120110100000 121 4053 10041307051317022222 1 2 599250
000244497003311012111101120110120210100000 111 4053 09041307051319022222 1 2 599250
000244497003311012111101120110130110100000 111 4053 14041307051318021222 1 2 31020
000244497003311012111101120110130210100000 111 4053 09041307051319022222 1 2 31020
000244497003311012111101120110130310100000 111 4053 09041307051320022222 1 2 31020
000244497003311012111101120110130410100000 111 4053 10041307051321022222 1 2 31020
000244497003311012111101120110130510100000 121 4053 15041307051318022222 1 2 31020
000244497003311012111101120110140110100000 111 4053 14041307051319021222 1 2 14100
000244437003311012111102110110120110100000 211 4049 10041303051315021222 1 2 111000
000244437003311012111102110110130110100000 611 4049 15041303051315022222 1 2 3700
000244437003311012111102110110130210100000 111 4049 15041303051312022222 1 2 3700
000244437003311012111102110110220110100000 111 4049 13041303051314022222 1 2 2197800
000244437003311012111102110110230110100000 411 4049 13041303051317022222 1 2 81400
000244437003311012111102110110230210100000 111 4049 15041303051316022222 1 2 81400
000244437003311012111102110110240110100000 111 4049 10041303051315022222 1 2 81400
000244437003311012111102110110240210100000 611 4940 10041303051315022222 1 2 81400
000244467003311012121201120110120110100000 111 4054 16031329031315022222 1 2 649250
000244467003311012121201120110120210100000 111 4054 19031329031314022212 1 2 649250
000244467003311012121201120110130110100000 111 4054 18031329031315022222 1 2 74200
000244467003311012121201120110130210100000 221 4054 16031329031314022222 1 2 74200
000244467003311012121201120110130310100000 111 4054 19031329031312022222 1 2 74200
000244467003311012121201120110130410100000 211 4054 16031329031314522222 1 2 74200
000244467003311012121201120110130510100000 211 4054 18031329031314522222 1 2 74200
000244467003311012121201120110140110100000 111 4054 18031329031313022222 1 2 26500
000244477003311012121202110110120110100000 111 4001 12031326031314022222 1 2 143775
000244477003311012121202110110120210100000 111 4001 13031326031313522222 1 2 143775
000244477003311012121202110110120310100000 211 4001 10031326031314022222 1 2 143775
000244477003311012121202110110120410100000 111 4001 11031326031314522222 1 2 143775
000244477003311012121202110110220110100000 111 4001 09031326031314022222 1 2 1091475
000244477003311012121202110110220210100000 111 4001 15031326031314522222 1 2 1091475
000244477003311012121202110110220310100000 111 4001 14031326031314522222 1 2 1091475
000244477003311012121202110110220410100000 111 4001 08031326031314022222 1 2 1091475
000244487003311012121203120110120110100000 221 4053 16031325041319022222 1 2 117000
000244487003311012121203120110130110100000 111 4053 15031325041319022222 1 2 3900
000244487003311012121203120110130210100000 221 4053 15031325041320022222 1 2 3900
000244487003311012121203120110130310100000 111 4053 16031325041320022222 1 2 3900
000244487003311012121203120110140110100000 111 4053 14031325041319022222 1 2 3900
000244487003311012121203120110140210100000 111 4053 15031325041319022222 1 2 3900
000244487003311012121203120110220110100000 321 4053 17031325041318022222 1 2 3806400
000244487003311012121203120110230110100000 111 4053 18031325041319021222 1 2 62400
000244247003311011131301110110120110100000 311 4049 15021311031321022222 1 2 781300
000244247003311011131301110110120210100000 121 4049 16021311031317022222 1 2 781300
000244247003311011131301110110130110100000 211 4049 20021311031320022222 1 2 120200
000244247003311011131301110110130210100000 111 4049 14021311031318022222 1 2 120200
000244247003311011131301110110130310100000 111 4049 15021311031318022222

[Kurt_Bremser]

First of all, 6-digit dates should be a thing of the past since 2000. Someone clearly needs their competence checked here.

Second, you can't use position-based input, as the positions change; the second column has 4 digits ('2129') in data line 15

Third, in data line 18 there is a '9' between the first and second identifiable columns, so they cannot be clearly separated. The same is true further to the right (lines 1 to 8, for example).

Return the data to the originator, with the following requests:

- use a proper date format with 4-digit years

- since columns have different lengths, either pad with sufficient spaces so fixed-column input can be used, or use a proper separator (blank, comma, semicolon, tab) between ALL columns

[anilgvdbm]

Dear Sir,

Thank you so much for the quick and valuable response. 

I already spoke with data originator he said they collected data in the DDMMYY format only so they collected in 2013 year.

So in the sample data 

you can see the 2 dates 050313130313 (05-03-13 and 13-03-13)

Si i need to extract date in this format.

Thanks again for quick response.

Regards,

Anil

[Kurt_Bremser]

Still does not fix the problem with the moving columns. The current format forces you to make guesses which data are where; this is ungood, as it may lead to wrong interpretations further down in the file.

One could try to make those guesses, but I much prefer correctly structured data.

Either have fixed columns (and, implicitly, a fixed record length!) or consistent column separators throughout.

Simply have your originator supply a record description with rules that cover all lines. In doing that, they will find their problems and correct them on their own, before the ruleset becomes larger than the dataset 😉