Reading a CSV file having double quotes in SAS DI

[vickys]

Hi,

Can some please help me t how to deal a csv file data like this in SAS DI.
I tried with comma as delimiter while create a file through external wizard.
When the file reader reads the data from the data file, its shows missing(.) for 2013 and 2014 data, as the collage has double quotes and has comma.

But, my file reader code has following code, which makes sense in handling the double quotes.

infile '/*/*/test_data.csv'
lrecl = 379
delimiter = ','
dsd
missover
firstobs = 2;
;

File data-:
year , dept, class, collage,total
2012, science, two, abc, 23
2012, maths, three, abc, 34
2013, science, four, "abc,xyz", 55
2013, social, five, "asd,jhg", 21
2014, maths, two, "qwe,lki", 37

Thanks,
Vicks

[Patrick]

@vickys

Please post the full data step as generated.

Below works for me - but that's not necessarily "100%" identical to what you get as generated code.

data test;
  infile datalines
    /*lrecl = 379*/
  delimiter = ','
  dsd
  missover
  firstobs = 2;
  ;

  attrib year length=8;
  attrib dept length=$10;
  attrib class length=$10;
  attrib collage length=$10;
  attrib total length=8;

  input year dept class collage total;

  datalines;
year , dept, class, collage,total
2012, science, two, abc, 23
2012, maths, three, abc, 34
2013, science, four, "abc,xyz", 55
2013, social, five, "asd,jhg", 21
2014, maths, two, "qwe,lki", 37
;
run;

[vickys]

Thanks Patrick,

 The code is similar to what you said and its working while I ran  in editor. But, the table reader output is showing missing values,I think something is gng wrng.

data work.WCPGEZNZ / view = work.WCPGEZNZ ;
infile '/sasdat/test_data.csv'
lrecl = 379
delimiter = ','
dsd
missover
firstobs = 2;
;
attrib YEAR length = $4;
attrib Dept length = $22;
attrib Class length = $39;
attrib College length = $51;
attrib Total length = 8;

input YEAR Dept Class College Total ;

run;

Thanks,

Vickys

[vickys]

Hi Patrick,

I just notice that the College is taking more space characters and the length should be around 200.

If i gave 200 then I am not getting missing values.

But, how can I handle that spaces in csv file.

Thanks,
Vickys

[Patrick · Accepted solution]

@vickys

I can't replicate the issue you describe so there must be something else going on which is not in your sample data.

For observations where you get a missing: Have you checked in the .csv if there are any special characters in this line like Tab or LF?

At least for testing purposes I'd create a table and not a view as output. That makes it easier to see issues in the log at the place where they actually occur and not only where the view gets executed.

[vickys]

Yes, its problem with Logical record length.

I just made that table and checked the log and rectified the length. It worked.

cheers.

Thanks Patrick