topic Re: CSV File Import : in SAS Programming

CSV File Import :

AliJ — Thu, 09 May 2013 11:49:15 GMT

Hi,

I have to import a Comma Seprated file into SAS but there is a problem with one coulm (A), Coulmn(A) has multiple values seprated by commas.

while importing SAS also reads these commas, so shift the values in subsequent coulmns(B,C,..). so the data become courrpt.

Can anay one please advice how can this exception(issue) be handle? so the multiple values in coulmn(A) remains in Coulmn(A).

SAS gurus your support in this would be highly appreciated

Regards

Ali J

Re: CSV File Import :

data_null__ — Thu, 09 May 2013 12:11:35 GMT

I don't believe you have a CSV because the specification for a CSV "says" that fields that contain the delimiter are to be quoted.

If you know the total number of fields you can count from the right to find the column where the first field should end. Then read that field with $VARYING and continue with the other fields.

Show example data.

Re: CSV File Import :

Amir — Thu, 09 May 2013 12:12:11 GMT

Hi,

Can you please provide some example problem data, the code you are using to read it and what results you want.

Regards,

Amir.

Re: CSV File Import :

RichardinOz — Thu, 09 May 2013 13:15:50 GMT

If column A always has the same number of commas (eg 2) then you can define extra temporary columns A1 A2 A3 to import the data; after the input statement you then need something like

A = catx (', ', A1, A2, A3) ;

which will restore the commas in a single column A. Follow with

Drop A1 A2 A3 ;

Otherwise this might work:

Infile xxx MISSOVER [etc] ;

Input @ ;

_Infile_ = reverse (_Infile_) ;

Input D C B A3 A2 A1 ;

D = left (reverse (D)) ;

C = left (reverse (C)) ;

...

A1 = left (reverse (A1)) ;

A = catx (', ', A1, A2, A3) ;

Drop A1 A2 A3 ;

Good luck.

Richard

Re: CSV File Import :

AliJ — Thu, 09 May 2013 13:43:37 GMT

Dear All,

Thanks for your replys.

Probably I did not explain the problem correctly. Please find attached sample data "SAMPL.csv"

There are coulmns from A to W , when I import data it create coulmns by commas but in coulmn 'N' there are multiple values(>=2, not fixed) also seprated by comma . SAS also read these values as different coulmns and shifts the value of adjacent coulmn. due to this extra coulmns cretae automatically and inapproprite data is filled in coulmns.

Kindly help me to resolve it, i am using SAS EG for data import

Regards

Re: CSV File Import :

RichardinOz — Thu, 09 May 2013 13:44:34 GMT

Here's another suggestion if you have differing numbers of commas in column A.

Create an array of your desired variables, then

Create an array of character variables t1 - tM where M = number of input variables (n) + maximum number of commas.

The length of each temporary variable must exceed the length of any input variable.

Input the data into the variables t1 - tM as text.

if t{n+1} is not blank then

t1 = catx (', ', t{1}, t{2}) ;

t{2} = t{3} ; etc to t{M-1} = t{M} ;

repeat until t{n+1} is blank

copy values to the array of desired values

Drop all the t{*} values

Use do while () or do until () to loop until t{n+1} is blank

Use do k = 1 to n to copy values from the t{*} array to the wanted values

Do any text to numeric conversion en route as required

Richard

Re: CSV File Import :

RichardinOz — Thu, 09 May 2013 13:58:27 GMT

I'm not sure how EG handles input where the apparent number of data items exceeds the number of columns defined in the input. I think you will have to write a data step anyway in a program module to handle this irregular data: modify my suggestion (above) to start at column N.

Richard

Re: CSV File Import :

cwilson — Thu, 09 May 2013 14:18:16 GMT

I agree with RichardinOz. The csv sample file was not built correctly. The text string for N should have been "11,15,17".

If you know that N is the only column that can have multiple values together, then write your data step/ input statement to read in extra fields at the end (X1, X2, X3, etc.) and then shift everything back, depending on the number of extra values found. I can also tell from the sample, that the number of extra values can vary from row to row.

I've had to deal with this myself, not fun.

You can do this using arrays, but a simple way to show what you are doing with your sample data:

(let's say if you know you can have up to 5 values in that multiple string)

* starting with the maximum number of extra fields ;

length N_string $16 ; * (to hold 5 2-digit numbers and commas);

input A B C etc... W X1 X2 X3 X4 ;

If X4 ^= ' ' then do ;

* we have 5 values for N ;

N_string = N || ',' || O || ',' || P || ',' || Q || ',' || R ;

O = S ;

P = T ;

*fill in the rest as above;

W = X4 ;

end ;

else if X3 ^= ' ' then do ;

and so on

Re: CSV File Import :

ballardw — Thu, 09 May 2013 14:25:34 GMT

Looking at the file example only, HOW do you know the problem is with column N? That makes me think you know more about the file source than you are telling here. As structured, the file is NOT CSV as mentioned data_null. In this case it is only the file extension.

If possible I would go to the source of the file and try to find out how it is created. Then work to see if the source can properly quote qualify the text.

Re: CSV File Import :

cwilson — Thu, 09 May 2013 14:29:41 GMT

I agree that my first choice would be to go back to the entity that generated the false "csv" file, but assuming that's not possible, or the source can't do what you want, and you KNOW what the data should be, you can work around it.

If more than one column can have multiple values, then you can't work around it.

Also, this is only going to work if all the columns after the problem column (column N in the sample) are the same data type. If you have a date column, or a text string in the columns that might be shifted, then you could lose data unless you read all columns in as string variables, and then convert the numbers.

Now that I think about it, I should have included conversions from the numbers to strings when contatenating N, O, P, etc.

Re: CSV File Import :

Ksharp — Thu, 09 May 2013 14:48:29 GMT

Maybe your csv file is not real CSV file. Use double quote around it .

Re: CSV File Import :

data_null__ — Thu, 09 May 2013 17:26:02 GMT

Assuming my assumptions are right. If this works you can add the proper INFORMATS to read the numbers and dates etc.

data csv;
   infile cards dsd column=col;
   input (a b c d e f g h i j k l m)(:$12.) @;
   column=col;
   CALL SCAN(_infile_,9, position, length,',','mb');
   lengthN = Position - col-1; 
   input n $varying32. lengthN +1 (o p q r s t u v w)(:$12.);
   cards; 
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W
C1,6,T,45600,31-MAR-13,3800,0,0,0,3800,31-JUL-03,31-JUL-05,S,15 ,17,0,,0,0,12,0,0,0,0
C2,54,E,5000000,30-NOV-12,0,0,0,0,0,06-MAY-11,30-SEP-12,S,11 ,18,0,,0,1,0,0,0,0,0
C3,6,T,27835,31-MAR-13,15467,0,0,0,15467,04-AUG-07,04-FEB-09,S,15 ,17,0,,0,0,9,0,0,0,0
C4,54,T,1000000,31-AUG-12,999932,0,0,0,1317036,09-AUG-00,31-JUL-09,S,11 ,15 ,17,0,,0,0,5,0,0,0,0
;;;;
   run;

Message was edited by: data _null_ "+1"

Re: CSV File Import :

cwilson — Thu, 09 May 2013 18:09:04 GMT

I love your idea, but a proc print shows that it is not quite working.

i thought perhaps the empty value of two commas together might be messing up the SCAN() (because SCAN might be calling two delimiters together as one delimiter), but it still didn't work when I added a space there. The variable "o" is not getting populated.

Re: CSV File Import :

data_null__ — Thu, 09 May 2013 18:37:48 GMT

I need to advance the pointer +1 after reading N.

Re: CSV File Import :

AliJ — Fri, 10 May 2013 04:53:38 GMT

I recive this data from extrenal source frequently and used to work on it, the external source often forget the quantifiers(" "), so to get the correct file waste time which is critical. i-e, i want to handle this exception at my end rather wasting time oftenly to get correct data.

help is required to write a procedure/data step to address this issue when it occurs.

Regards

Ali J

Re: CSV File Import :

AliJ — Fri, 10 May 2013 05:53:47 GMT

Thanks a lot ! data_null_ and cwilson. it really work well.

Re: CSV File Import :

DanWALDO — Fri, 10 May 2013 15:50:25 GMT

I know that this is really simplistic and clunky (I never really got out of SASv6 mode!), but perhaps it can start you down a trail. Some of the code is bloated to provide intermediate outputs, and could easily be replaced. The assumption is that column N is the field with multiple commas, and that quotation marks are always omitted.

data test;

infile "C:\My SAS Files\sample.csv" lrecl=256 pad;

length aa a b c d e f g h i j k l m n o p q r s t u v w $12;

length ipt ript tipt $256.;

input ipt $char256.;

ript=trim(reverse(ipt));

array field {*} a b c d e f g h i j k l m n o p q r s t u v w;

do ii=dim(field) to 15 by -1;

jj=index(ript,",");

if jj=1 then do;

field(ii)=" ";

ript=substr(ript,2);

end;

else do;

field{ii}=reverse(substr(ript,1,jj-1));

ript=trim(substr(ript,jj+1));

end;

tipt=left(reverse(ript));

do ii=1 to 13;

jj=index(tipt,",");

if jj=1 then do;

field(ii)=" ";

tipt=substr(tipt,2);

end;

else do;

field{ii}=substr(tipt,1,jj-1);

tipt=trim(substr(tipt,jj+1));

end;

n=trim(tipt);

run;

Re: CSV File Import :

AliJ — Wed, 22 May 2013 08:02:31 GMT

DanWaldo !

your approch and code was awsome and i had sucefully implemented it with some customization