BookmarkSubscribeRSS Feed
anilgvdbm
Quartz | Level 8

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

 

 

 

 


SAS date error.JPG
3 REPLIES 3
Kurt_Bremser
Super User

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
Quartz | Level 8

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
Super User

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 😉

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 946 views
  • 3 likes
  • 2 in conversation