BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vickys
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

5 REPLIES 5
Patrick
Opal | Level 21

@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
Obsidian | Level 7

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
Obsidian | Level 7

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
Opal | Level 21

@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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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