I updated the code in the previous post. You can preview the data outside a job on it's own but there's no log then. I updated both metadata objects to point at the actual physical file rather than a macro variable and got the exact same results (I left it as &filename. in the sample code as I can't post filenames/paths here). I noticed the Record lengths were different, although that wasn't the issue as longer records without commas were being read in fine (the comma was at position 77), however, updating the length seems to have forced some kind of refresh and that metadata object now works too.   DI has always sadly been the blacksheep of the SAS family (even though it's great) and can have bugs like this sometimes where it can get caught up and have weird metadata bugs, often coming back the next day can solve them but this one stuck around, I was hesitant to start making changes to a process that's run for 5+ years, but altering the file metadata seems to have done it (I'll stick with the new metadata objects anyway to be safe). I found a bug in DI years ago that after a lot of back and forth SAS accepted DI was generating incorrect code and for years kept been told it was being fixed in the next version and I don't think it ever was, I gave up after a few years of asking and our Admin have implemented a workaround. ... View more

I made a new job and added the old and new metadata objects each linked to a file reader.  Old metadata object, doesn't work data work.W1C68GP0 / view = work.W1C68GP0 ; infile &Filename. lrecl = 256 delimiter = ',' dsd missover firstobs = 2; ; attrib Period length = $100; attrib OrgCode length = $9; attrib Quarter length = 8; attrib Allocation length = $150; attrib SpendServices length = $150; attrib SpendPharma length = $150; attrib SpendOther length = $150; attrib SetQuitDateLastYear length = $150; attrib SetQuitDateThisYear length = $150; attrib UnknownQuitStatusThisYear length = $150; attrib SuccessfulQuittersLastYear length = $150; attrib SuccessfulQuittersThisYear length = $150; attrib BreachComments length = $1000 format = $char1000. informat = $char1000.; input Period OrgCode Quarter Allocation SpendServices SpendPharma SpendOther SetQuitDateLastYear SetQuitDateThisYear UnknownQuitStatusThisYear SuccessfulQuittersLastYear SuccessfulQuittersThisYear BreachComments; run;   New metadata object, does work   data work.W1CAEHBW / view = work.W1CAEHBW ; infile &Filename. lrecl = 1250 delimiter = ',' dsd missover firstobs = 2; ; attrib Period length = $100; attrib OrgCode length = $9; attrib Quarter length = 8; attrib Allocation length = $150; attrib SpendServices length = $150; attrib SpendPharma length = $150; attrib SpendOther length = $150; attrib SetQuitDateLastYear length = $150; attrib SetQuitDateThisYear length = $150; attrib UnknownQuitStatusThisYear length = $150; attrib SuccessfulQuittersLastYear length = $150; attrib SuccessfulQuittersThisYear length = $150; attrib BreachComments length = $1000 format = $char1000. informat = $char1000.; input Period OrgCode Quarter Allocation SpendServices SpendPharma SpendOther SetQuitDateLastYear SetQuitDateThisYear UnknownQuitStatusThisYear SuccessfulQuittersLastYear SuccessfulQuittersThisYear BreachComments; run;   The code looks identical but you can see in the results one works, one doesn't.     ... View more

I'm back sorry, so it's sorted now, but I'm not overly pleased why. Essentially I tried tweaking the external file metadata but no joy, however if I copied it then repointed it at some of the data in a new file it worked. I then decided to make new external file metadata objects pointing at the original files, I used identical field names, formats etc. I verified the infile code was identical, but for some reason the new ones work fine and the old ones don't. I've been through every single thing I can check and they look identical, but one loads in the full text and one doesn't. I'm baffled but at least it's solved.   I had two different files with different layouts and both had the same issue and this solved it for both. Just another 'SASism' to deal with.   Thanks for the help. ... View more

LRECL is ok as it's reading in longer where there's not a comma. As far as I can tell opening the csv in notepad++ they appear to be regular quotes. ... View more

Thanks, I've had a look though and the infile statement already has dsd as an option.   infile "&Filename." lrecl = 256 delimiter = ',' dsd missover firstobs = 2;   ... View more

Right, but I'm telling it to do it as a character, so why is it converting it to US format and dropping leading zeroes. ... View more

It's a full SAS Grid setup so on the Windows Server Grid servers I guess, they're UK Locality. I guess I still don't get why it's trying to convert them when it's reading as a character string, they're correct when reading as Date9.   dbsastype should override the default format (probably date) and force it to use what I specify which is character, so why is it converting it incorrecly and dropping characters. ... View more

The excel files aren't being generated by SAS, they're my source data. It's hundreds of files a week so there's a lot of variations hence me trying to read as a character string so I can clean them up a bit (basically compress out all letters). As you can see below it IS transforming the data when reading in as a character. Excel:   SAS (using the Char(10) code I put above: SAS (using Date9. I put above, but this fails for entries with characters):   I can't provide a sample file due to the nature of the data and we can't change the submission files as it's a live national system with hundreds of users. As much as we prefer csv etc. it isn't user friendly enough for the users in the field. Basically I just want a raw import of the data as it is in Excel without SAS do anything to it. ... View more

Thanks, but as it's an automated system handing hundreds of submitted files a week we can't manually change columns (besides it's already set as a date) or convert each to a csv. If the date is displaying correctly in Excel with the zero, it doesn't seem excel is dropping it, so SAS reading it as a character string should surely capture as is, I'm not sure why it's dropping a zero out a character field. Excel already has it set as a date so I need a way for either character to keep the format and not drop the zero (I assumed character should just get the string). Or a way to drop any none dates from it as I read it in, instead of it either warning or out right failing.   I'm seeing it by running my code and checking the output data tab in EG, it's definitely a character field and has dropped the zero. ... View more

Thanks, I got it working with the below, I had to change it to go Numeric to Chararter as Numeric to Numeric didn't work as on MONTHDAY it was still dropping leading zeroes (not an issue with YEARMONTH).   CASE WHEN DOB ^= . THEN cats(put(year(datepart(DOB)),z4.),put(month(datepart(DOB)),z2.)) ELSE "" END   I tried put(datepart(dob),yymmn6.) It got upset I think because it was going datetime to a straight numeric. ... View more