topic Re: Reading a CSV file having double quotes in SAS DI in SAS Data Management

Reading a CSV file having double quotes in SAS DI

vickys — Wed, 20 Jun 2018 03:46:07 GMT

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

Re: Reading a CSV file having double quotes in SAS DI

Patrick — Wed, 20 Jun 2018 04:02:37 GMT

@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;

Re: Reading a CSV file having double quotes in SAS DI

vickys — Thu, 21 Jun 2018 01:36:30 GMT

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

Re: Reading a CSV file having double quotes in SAS DI

vickys — Thu, 21 Jun 2018 02:19:42 GMT

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

Re: Reading a CSV file having double quotes in SAS DI

Patrick — Thu, 21 Jun 2018 03:26:00 GMT

@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.

Re: Reading a CSV file having double quotes in SAS DI

vickys — Thu, 21 Jun 2018 06:25:07 GMT

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