topic Re: reading raw data in ODS and Base Reporting

reading raw data

suraj — Tue, 10 Oct 2017 13:37:25 GMT

I am reading a comma separated raw data file which has blank spaces in between and data is in " ". I am able to read all the columns using infile statement but I got stuck at one point that is one of the column has values which are tabs of spaces in between which is making it a larger length column. It is a sql server output data where the length of that column in $4000 i have taken the same in SAS as well but without reading the whole it jumps to the next column and put the remaining text in the next column. Please help me on this. Putting the sample value for that column:

$12,8 to your account.

Some Tracking Number: 4556556

Other Tracking Number: 4554545454545

Other input Number: 47876546321654

Re: reading raw data

ballardw — Tue, 10 Oct 2017 14:33:04 GMT

Show the code you are using to read the file.

Possible causes of issues without seeing code are 1) lrecl option on an infile statement not large enough to read the entire line, 2) the length of the variable reading that long column was not set large enough to hold the values

3) possibly you need the DSD option to allow reading commas inside a quoted string.

Post example CSV data as actual TXT file or copy from the CSV opened with Notepad, Wordpad or other text editor.. It looks like you copied something from Excel and Excel does not actually show the content of CSV as it strips out details. It would also be a good idea to paste the example data into a code box opened using the forum {I} menu icon to prevent the message window from reformatting text.

Re: reading raw data

suraj — Wed, 11 Oct 2017 09:54:37 GMT

Hello @ballardw,

Thanks for your reply. I am sharing the code with you with data file the data file has only one row.

data New;
infile '/home/temp/sample.csv' dlm="|" dsd firstobs=3 truncover LRECL=1000000 ;
input
Col1 :$100.
Col2 :$100.
Col3 :$100.
Col4 :$100.
Col5 :$100.
Col6 :$100.
Col7 :$100.
Col8 :$100.
Col9 :$100.
Col10 :$100.
Col11 :$100.
Col12 :$100.
Col13 :$100.
Col14 :$100.
Col15 :$100.
Col16 :$100.
Col17 :$100.
Col18 :$100.
Col19 :$100.
Col20 :$100.
Col21 :$100.
Col22 :$100.
Col23 :$100.
Col24 :$100.
Col25 :$100.
Col26 :$100.
Col27 :$100.
Col28 :$100.
Col29 :$100.
Col30 :$100.
Col31 :$100.
Col32 :$100.
Col33 :$100.
Col34 :$100.
Col35 :$100.
Col36 :$100.
Col37 :$100.
Col38 :$100.
Col39 :$100.
Col40 :$100.
Col41 :$100.
Col42 :$100.
Col43 :$100.
Col44 :$100.
Col45 :$100.
Col46 :$100.
Col47 :$100.
Col48 :$100.
Col49 :$100.
Col50 :$100.
Col51 :$100.
Col52 :$100.
Col53 :$100.
Col54 :$5000.
Col55 :$5000.
Col56 :$5000.
Col57 :$5000.
Col58 :$5000.
Col59 :$5000.
Col60 :$5000.
Col61 :$5000.
Col62 :$5000.
Col63 :$5000.
Col64 :$5000.
Col65 :$5000.
Col66 :$5000.
Col67 :$5000.
Col68 :$5000.
Col69 :$5000.
Col70 :$5000.
Col71 :$5000.

;

run;