SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Reading a CSV file having double quotes in SAS DI

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Reading a CSV file having double quotes in SAS DI

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


Accepted Solutions
Solution
‎06-21-2018 02:25 AM
Respected Advisor
Posts: 4,779

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

[ Edited ]

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,779

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

[ Edited ]

@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;
Occasional Contributor
Posts: 15

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

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

Occasional Contributor
Posts: 15

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

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

Solution
‎06-21-2018 02:25 AM
Respected Advisor
Posts: 4,779

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

[ Edited ]

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

Occasional Contributor
Posts: 15

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 187 views
  • 1 like
  • 2 in conversation