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
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.
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;
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
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.