SAS Programming PROC Import CSV files issues

[kmin871]

%web_drop_table(WORK.breastcancer);


FILENAME REFFILE '/home/u60818803/sasuser.v94/breast-cancer.csv';

PROC IMPORT DATAFILE=REFFILE
	DBMS=CSV
	OUT=WORK.breastcancer;
	GETNAMES=NO;
RUN;

PROC CONTENTS DATA=WORK.breastcancer; RUN;


%web_open_table(WORK.breastcancer);

Hello, I am import a csv file to sas but once I do using code above. It only gives me one variable. The csv file has more than one variables. Could anyone assist?

[andreas_lds]

Please post the log using the </> (insert code). You may want to add the option "replace" to the proc import statement.

[kmin871]

 1          OPTIONS NOSYNTAXCHECK;
 2          TITLE;
 3          FOOTNOTE;
 4          OPTIONS LOCALE=en_US DFLANG=LOCALE;
 5          DATA _NULL_;
 6          RUN;
 
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              468.84k
       OS Memory           26284.00k
       Timestamp           02/25/2022 07:34:41 AM
       Step Count                        27  Switch Count  0
       Page Faults                       0
       Page Reclaims                     24
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 7          OPTIONS VALIDVARNAME=ANY;
 8          OPTIONS VALIDMEMNAME=EXTEND;
 9          FILENAME _HTMLOUT TEMP;
 10         FILENAME _GSFNAME TEMP;
 11         FILENAME _DATAOUT TEMP;
 12         %LET SYSCC=0;
 13         %LET _CLIENTAPP='SAS Studio';
 14         %LET _CLIENTAPPABREV=Studio;
 15         %LET _CLIENTAPPVERSION=3.8;
 16         %LET _CLIENTVERSION=3.8;
 17         %LET _CLIENTMODE=wip;
 18         %LET _SASSERVERNAME=%BQUOTE(SASApp);
 19         %LET _SASHOSTNAME=%BQUOTE(odaws01-usw2);
 20         %LET _SASPROGRAMFILEHOST=%BQUOTE(odaws01-usw2);
 21         %LET _CLIENTUSERID=%BQUOTE(u60818803);
 22         %LET _CLIENTUSERNAME=%BQUOTE(u60818803);
 23         %LET CLIENTMACHINE=%BQUOTE(108-87-103-239.LIGHTSPEED.SNANTX.SBCGLOBAL.NET);
 24         %LET _CLIENTMACHINE=%BQUOTE(108-87-103-239.LIGHTSPEED.SNANTX.SBCGLOBAL.NET);
 25         %let SASWORKLOCATION="%sysfunc(getoption(work))/";
 26         FILENAME _CWD '.';
 27         DATA _NULL_;
 28         CALL SYMPUT('_SASWORKINGDIR',PATHNAME('_CWD'));
 29         RUN;
 
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              502.93k
       OS Memory           26284.00k
       Timestamp           02/25/2022 07:34:41 AM
       Step Count                        28  Switch Count  0
       Page Faults                       0
       Page Reclaims                     25
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           8
       
 
 30         FILENAME _CWD;
 NOTE: Fileref _CWD has been deassigned.
 31         
 32         %LET _SASPROGRAMFILE = %NRQUOTE(%NRSTR(Program 1));
 33         %LET _BASEURL = %BQUOTE(https://odamid-usw2.oda.sas.com/SASStudio/);
 34         %LET _EXECENV=SASStudio;
 35         DATA _NULL_;
 36         CALL SYMPUT("GRAPHINIT","");
 37         CALL SYMPUT("GRAPHTERM","");
 38         RC=TSLVL('SASXGOPT','N');
 39         _ERROR_=0;
 40         IF (RC^=' ') THEN DO;
 41         CALL SYMPUT("GRAPHINIT","GOPTIONS RESET=ALL GSFNAME=_GSFNAME;");
 42         CALL SYMPUT("GRAPHTERM","GOPTIONS NOACCESSIBLE;");
 43         END;
 44         RUN;
 
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              508.25k
       OS Memory           26284.00k
       Timestamp           02/25/2022 07:34:41 AM
       Step Count                        29  Switch Count  0
       Page Faults                       0
       Page Reclaims                     25
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 45         DATA _NULL_;
 46         RC=SYSPROD("PRODNUM002");
 47         IF (RC^=1) THEN DO;
 48         CALL SYMPUT("GRAPHINIT","");
 49         CALL SYMPUT("GRAPHTERM","");
 50         END;
 51         RUN;
 
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              507.12k
       OS Memory           26284.00k
       Timestamp           02/25/2022 07:34:41 AM
       Step Count                        30  Switch Count  0
       Page Faults                       0
       Page Reclaims                     25
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 52         %LET _DATAOUT_MIME_TYPE=;
 53         %LET _DATAOUT_NAME=;
 54         %LET _DATAOUT_TABLE=;
 55         %LET _DATAOUT_URL=;
 56         %SYMDEL _DATAOUT_MIME_TYPE _DATAOUT_NAME _DATAOUT_URL _DATAOUT_TABLE;
 57         %LET _SASWS_ = %BQUOTE(/home/u60818803);
 58         %LET _SASWSTEMP_=%BQUOTE(/home/u60818803/.sasstudio/.images/7333eed4-b490-4b58-b46a-90e12f6bc5da);
 59         ODS LISTING CLOSE;
 60         ODS AUTONAVIGATE OFF;
 61         ODS GRAPHICS ON;
 62         ODS HTML5 (ID=WEB) DEVICE=PNG GPATH="&_SASWSTEMP_" ENCODING=utf8  FILE=_HTMLOUT (TITLE='Results: Program 1')
 62       ! STYLE=Htmlblue OPTIONS(BITMAP_MODE='INLINE' OUTLINE='ON' SVG_MODE='INLINE'
 62       ! CSS_PREFIX='.ods_7333eed4-b490-4b58-b46a-90e12f6bc5da' BODY_ID='div_7333eed4-b490-4b58-b46a-90e12f6bc5da' );
 NOTE: Writing HTML5(WEB) Body file: _HTMLOUT
 63         &GRAPHINIT;
 64         OPTIONS FIRSTOBS=1;
 65         OPTIONS OBS=MAX;
 66         OPTIONS DTRESET DATE NUMBER NOTES;
 67         OPTIONS NOSYNTAXCHECK;
 68         
 69         /* Generated Code (IMPORT) */
 70         /* Source File: breast-cancer.csv */
 71         /* Source Path: /home/u60818803/sasuser.v94 */
 72         /* Code generated on: 2/25/22, 1:33 AM */
 73         
 74         %web_drop_table(WORK.IMPORT);
 NOTE: Table WORK.IMPORT has been dropped.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.01 seconds
       memory              193.62k
       OS Memory           28068.00k
       Timestamp           02/25/2022 07:34:41 AM
       Step Count                        31  Switch Count  2
       Page Faults                       0
       Page Reclaims                     45
       Page Swaps                        0
       Voluntary Context Switches        9
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 75         
 76         
 77         FILENAME REFFILE '/home/u60818803/sasuser.v94/breast-cancer.csv';
 78         
 79         PROC IMPORT DATAFILE=REFFILE
 80         DBMS=CSV
 81         REPLACE
 82         OUT=WORK.IMPORT;
 83         GETNAMES=NO;
 84         RUN;
 
 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
 WORK.PARMS.PARMS.SLIST.
 85          /**********************************************************************
 86          *   PRODUCT:   SAS
 87          *   VERSION:   9.4
 88          *   CREATOR:   External File Interface
 89          *   DATE:      25FEB22
 90          *   DESC:      Generated SAS Datastep Code
 91          *   TEMPLATE SOURCE:  (None Specified.)
 92          ***********************************************************************/
 93             data WORK.IMPORT    ;
 94             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
 95             infile REFFILE delimiter = ',' MISSOVER DSD  ;
 96                informat VAR1 $69. ;
 97                format VAR1 $69. ;
 98             input
 99                         VAR1  $
 100            ;
 101            if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 102            run;
 
 NOTE: The infile REFFILE is:
       Filename=/home/u60818803/sasuser.v94/breast-cancer.csv,
       Owner Name=u60818803,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=24Feb2022:21:24:28,
       File Size (bytes)=19515
 
 NOTE: 286 records were read from the infile REFFILE.
       The minimum record length was 55.
       The maximum record length was 73.
 NOTE: The data set WORK.IMPORT has 286 observations and 1 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              9342.93k
       OS Memory           35616.00k
       Timestamp           02/25/2022 07:34:41 AM
       Step Count                        32  Switch Count  2
       Page Faults                       0
       Page Reclaims                     142
       Page Swaps                        0
       Voluntary Context Switches        12
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 286 rows created in WORK.IMPORT from REFFILE.
   
   
   
 NOTE: WORK.IMPORT data set was successfully created.
 NOTE: The data set WORK.IMPORT has 286 observations and 1 variables.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.05 seconds
       user cpu time       0.05 seconds
       system cpu time     0.00 seconds
       memory              9342.93k
       OS Memory           35616.00k
       Timestamp           02/25/2022 07:34:41 AM
       Step Count                        32  Switch Count  7
       Page Faults                       0
       Page Reclaims                     3088
       Page Swaps                        0
       Voluntary Context Switches        69
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           312
       
 
 103        
 104        PROC CONTENTS DATA=WORK.IMPORT; RUN;
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           0.03 seconds
       user cpu time       0.03 seconds
       system cpu time     0.01 seconds
       memory              1737.84k
       OS Memory           30636.00k
       Timestamp           02/25/2022 07:34:41 AM
       Step Count                        33  Switch Count  0
       Page Faults                       0
       Page Reclaims                     215
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           16
       
 
 105        
 106        
 107        %web_open_table(WORK.IMPORT);
 108        
 109        OPTIONS NOSYNTAXCHECK;
 110        ODS HTML CLOSE;
 111        &GRAPHTERM; ;*';*";*/;RUN;QUIT;
 112        QUIT;RUN;
 113        ODS HTML5 (ID=WEB) CLOSE;
 114        
 115        FILENAME _GSFNAME;
 NOTE: Fileref _GSFNAME has been deassigned.
 116        DATA _NULL_;
 117        RUN;
 
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              468.84k
       OS Memory           27820.00k
       Timestamp           02/25/2022 07:34:41 AM
       Step Count                        34  Switch Count  0
       Page Faults                       0
       Page Reclaims                     24
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 118        OPTIONS NOTES STIMER SOURCE SYNTAXCHECK;
 119        
 User: u60818803

[Kurt_Bremser]

Never rely on the vagaries of PROC IMPORT when you don't need it (you don't need it for Comma Separated Volumes, reading those is straight forward in a data step).

Your file has the whole line (!) enclosed in a pair of double quotes, so IMPORT thinks you only have one value.

This data step reads it fine:

data breast_cancer;
infile "~/breast-cancer.csv" dlm=",";
input
  var1 :$20.
  var2 :$5.
  var3 :$7.
  var4 :$5.
  var5 :$5.
  var6 :$3.
  var7
  var8 :$5.
  var9 :$9.
  var10 :$3.
;
var1 = substr(var1,2);
var10 = compress(var10,'"');
run;

The last two statements remove the starting and trailing double quote.

Replace the names with those that you have in the documentation of the csv file.

[kmin87]

Is there a way to do it with proc import?

[Tom · Accepted solution]

---

@kmin87 wrote:
Is there a way to do it with proc import?

---

Fix the file first to remove the extra quotes.  Then PROC IMPORT should be able to read it.

This might work.

filename original 'myfile.csv';
filename fixed 'myfile_noquotes.csv';
data _null_;
  infile original dsd truncover ;
  input line :$32767. ;
  file fixed;
  put line;
run;
proc import datafile=fixed dbms=csv .....

[Kurt_Bremser]

---

@kmin87 wrote:
Is there a way to do it with proc import?

---

If you like to waste time fixing all the issues caused by the guessing of PROC IMPORT, yes. If you want to get it right (and consistent at that), you write the data step yourself like I did.