Reading in comma-delimited values in SAS

[jc3992]

data balances;
proc import '/folders/myfolders/Week_5/balances.csv' out=balances DBMS=csv replace ;
proc print data=balances;
run;

customer, checking, savings, mortgage, credit_card
Smith, 1000.00, 4000.00, 150000.00, 500.00
Jones, 973.78, 2613.44, ., 140.48

Hello everyone,

The data set is as above, and I used the code above.

The LOG showed as below:

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         data balances;
 
 NOTE: The data set WORK.BALANCES has 1 observations and 0 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 74         proc import '/folders/myfolders/Week_5/balances.csv' out=balances DBMS=csv replace ;
 
 22: LINE and COLUMN cannot be determined.
 NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
 ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE, 
               _DEBUG_.  
 200: LINE and COLUMN cannot be determined.
 NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
 ERROR 200-322: The symbol is not recognized and will be ignored.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 NOTE: The SAS System stopped processing this step because of errors.
 
 
 75         proc print data=balances;
 76         run;
 
 NOTE: No variables in data set WORK.BALANCES.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 77         
 78         
 79         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 92           

[Reeza · Accepted solution]

proc import datafile='/folders/myfolders/Week_5/balances.csv' out=balances DBMS=csv replace ; guessingrows=max; run;

proc print data=balances; run;

[Tom]

The first line starts and data step, but you never did anything else for that data step so it makes a dataset with one observation and zeor variables.

The second line is using PROC IMPORT to read the CSV into a dataset, but it is not working.

The error message is strange and might indicate some type of strange character in your program code?

Try running the PROC IMPORT step by itself and see if it works.

But you could just as easily read the file with your data step and not worry about getting PROC IMPORT to work.

data balances;
  infile '/folders/myfolders/Week_5/balances.csv' dsd truncover firstobs=2 ;
  length customer $20 checking savings mortgage credit_card 8;
  input customer checking savings mortgage credit_card ;
run;

[jc3992]

 Thanks~

But I hope to avoid the "formatting" problems.

I mean, I always cannot remember what length or code I should use for those different kinds of data.

It is really a trouble for me to be honest.

So I like to use import or set.

[RW9]

"

But I hope to avoid the "formatting" problems.

I mean, I always cannot remember what length or code I should use for those different kinds of data.

"

Proc import is a guessing procedure, it shouldn't really be used in anything more than testing, or to get a shell of code.  A datastep import where you specify lengths, formats, informats, and exactly how to read the data, makes you code robust, repeatable, and transparent.  This should not be a problem as you will have setup a proper data transfer, which will have a data transfer agreement detailing all this information, and will be signed by the sender and yourself which constitutes both the documentation and the agreement.

Of course if you do none of the above, then what you get out of this, well who knows.  Much the same as if you take you car to the garage and ask them to fix something, without specifying in a contract what there is to be fixed, they could paint it bright yellow and replace the wheels with cheese.

[jc3992]

I understand. thanks! 

[ballardw]

---

@jc3992 wrote:

 Thanks~

But I hope to avoid the "formatting" problems.

I mean, I always cannot remember what length or code I should use for those different kinds of data.

It is really a trouble for me to be honest.

So I like to use import or set.

---

There a multiple advantages to @Tom's solution if you are reading multiple sets of the same layout. If you save the data step then next time you need to read a file you change the infile to point to the new file and change the output data set name (if saving to a permanent library).

All of the variables will have the same characteristics in each file read with the same data step. You can add in additional code such as providing variable labels for documentation and more meaningful output, to check data such as reporting on missing or unexpected values and data transformations as needed.

Proc Import guesses everytime it reads a file. So the lengths of character variables may change. If you don't pay attention to data types then you may have data one time that is numeric as all of the values only consisted of digits but then the next time you have character values. So combining data sets becomes an exercise in 'fixing data' that would have been correct when read properly.

Also for a new file layout go ahead and use Proc Import, copy the data step code generated from the log  into the editor and save it. Then verify that the lengths are as you expect for character variables, the numeric/character are correct and match your expectations.

[jc3992]

May I ask what does you mean by "copy...to the editor"?

Is there a text editor in SAS Studio?

For instance, Notepad for Windows.

I use TextWrangler for Mac.

Did you mean that kind of editor?

Thanks~

[Reeza]

Yes. At the top right corner, you can choose to interact with SAS Studio as a process flow or a programming mode. The programmer mode has an editor where you're typing and submitting code. 

[Reeza]

---

@jc3992 wrote:

 Thanks~

But I hope to avoid the "formatting" problems.

I mean, I always cannot remember what length or code I should use for those different kinds of data.

It is really a trouble for me to be honest.

So I like to use import or set.

---

Garbage in, garbage out. Verifying your data is the most important step in your process.