DATA Step, Macro, Functions and more

Import csv file with column with long text

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Import csv file with column with long text

Hi Everyone,

1 of the colum named "Note" is text like that "Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 yrcyk"

 

The situation is that the first record is blank.

 

Therefore when I import the error show up "ERROR: Import unsuccessful. See SAS Log for details."

 

I think it is caused by the fact that the first record is blank. 

 

When I copy a long text and put it in the first record, SAS import sucessfully. 

 

Is there any way to fix this error?

 

Thanks you for your help.

 

HC

 

proc import datafile="C:\Book1.csv" 
out=myfile dbms=csv replace; 
getnames=yes; 
run; 

Accepted Solutions
Solution
‎05-25-2017 12:10 PM
PROC Star
Posts: 7,363

Re: Import csv file with column with long text

You left off the colon before Status in your input statement. However that, in itself, won't correct all of the problems. Some of your dates have values of Pending. If a missing value of .P will suffice for those records, the following should work for you:

data want1 (drop=_:);
  infile '/folders/myfolders/Time Hour Report_sample.csv'
    dlm=',' dsd truncover firstobs=2;
  input DOW :$3. Task :$100. Type :$10. Approver :$10. _check :$10. Description :$100.;
  if anydigit(first(_check)) then Status=input(_check,mmddyy10.);
  else Status=.P;
  format status mmddyys10.;
run;

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
Respected Advisor
Posts: 3,893

Re: Import csv file with column with long text

@hhchenfx

You could use "datarow=2" or you could use a data step instead which gives you full control how to read the data. You could use an import wizard (in SAS EG or SAS Studio) to generate such data step code.

 

You're not the first encountering this challenge. Here one of the discussions around the same topic.

https://communities.sas.com/t5/Base-SAS-Programming/Proc-Import-to-Read-CSV-File-Variable-Names-Star...

 

PROC Star
Posts: 7,363

Re: Import csv file with column with long text

I agree with @Patrick, other than I think you want to set datarow to 3. e.g.:

 

proc import   datafile="c:\Book1.csv" 
  out=myfile dbms=csv replace;
  datarow=3;
  getnames=yes; 
run; 

However, the import ran correctly for me whether I did or didn't include the datarow statement. As such, I think you'd have to post your log in order for anyone to be able to see what problem you actually confronted. We might also have to see your csv file.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 17,835

Re: Import csv file with column with long text

99% of the time it's just faster to take the code generated (see the log) and modify that manually. This way your code will run exactly the same in the future. You can't guarantee that if you use PROC IMPORT. 

 

To copy the code from the log without line numbers hold down the ALT or SHIFT key when highlighting the text, I think it's ALT..

 

 

Super Contributor
Posts: 371

Re: Import csv file with column with long text

I meant the first several record has the "note" value blank.

And I have to keep all record.

 

HC

Super Contributor
Posts: 371

Re: Import csv file with column with long text

The data is like that

 

Code Status Note
RF 12/30/2016  
RF 12/30/2016  
RF 12/30/2016  
EH Pending Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
EH Pending Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
EH Pending Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
EH Pending Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
EH Pending Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
EH Pending Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
EH Pending Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
TB Pending PreProduction and Emergency Releae of MHV 2016.4.0.18
TB Pending PreProduction and Emergency Releae of MHV 2016.4.0.18
Super User
Posts: 6,939

Re: Import csv file with column with long text

Post the log, so we can show you how to extract the datastep code from it and how to modify it so it works without problems.

 

And post the first 5 lines of your csv "as is", with the comma separators. Use the {i} icon to open a window that preserves the text formatting.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 371

Re: Import csv file with column with long text

My data is below and I also attach just in case you need.

 

I rarely work with import so I have very limited experience with it.

 

Here is the code I try

 

*Time Hour Report;
proc import datafile='C:\Users\Time Hour Report_sample.csv' 
 out=TimeHour dbms=csv replace; 
getnames=yes; 
run; 


data splitsmp;
infile 'C:\Users\Time Hour Report_sample.csv';
input DOW	$ Task	$ Type $	Approver $	Status	Description $;run;

 

 

 

DOW,Task,Type,Approver,Status,Description
Mon,VSH,,RF,11/28/2016,
Wed,XTH - Subject Matter Expert II,,RF,11/28/2016,
Thu,XTH - Subject Matter Expert II,,RF,11/28/2016,
Fri,XTH - Subject Matter Expert II,,RF,11/28/2016,
Wed,XTH - Subject Matter Expert II,Reg,EH,12/5/2016,
Thu,XTH - Subject Matter Expert II,Reg,EH,12/5/2016,
Fri,XTH - Subject Matter Expert II,Reg,EH,12/5/2016,
Sat,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Wed,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Thu,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Fri,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Sun,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Wed,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Thu,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Fri,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Sat,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Sun,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Mon,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Tue,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Wed,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Thu,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Fri,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Sat,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Sun,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Mon,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Wed,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Fri,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Sat,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Sun,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Mon,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Tue,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Wed,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Thu,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Fri,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Mon,VZ Integration - CM,Reg,TB,Pending,PreProduction and CUT Releae of MHV 2016.4.0.18
Tue,SXV Cloud Integration - CM,Reg,TB,Pending,PreProduction and CUT Releae of MHV 2016.4.0.18
Wed,CMTr Cloud Integration - CM,Reg,TB,Pending,PreProduction and Emergency Releae of VZN 2016.4.0.18

Attachment
Super User
Posts: 6,939

Re: Import csv file with column with long text

Well, you didn't post the log as I requested, so I can't show you the way from proc import to a working data step, but I wrote this data step that reads your data without problems:

data want;
infile datalines4 dlm=',' dsd truncover;
input DOW :$3. Task :$25. Type :$3. Approver :$2. Status :$10. Description :$100.;
datalines4;
Mon,VSH,,RF,11/28/2016,
Wed,XTH - Subject Matter Expert II,,RF,11/28/2016,
Thu,XTH - Subject Matter Expert II,,RF,11/28/2016,
Fri,XTH - Subject Matter Expert II,,RF,11/28/2016,
Wed,XTH - Subject Matter Expert II,Reg,EH,12/5/2016,
Thu,XTH - Subject Matter Expert II,Reg,EH,12/5/2016,
Fri,XTH - Subject Matter Expert II,Reg,EH,12/5/2016,
Sat,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Wed,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Thu,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Fri,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Sun,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Wed,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Thu,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Fri,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Sat,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Sun,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Mon,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Tue,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Wed,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Thu,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Fri,XTH - Subject Matter Expert II,Reg,RF,12/22/2016,
Sat,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Sun,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Mon,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Wed,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Fri,XTH - Subject Matter Expert II,Reg,RF,12/30/2016,
Sat,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Sun,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Mon,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Tue,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Wed,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Thu,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Fri,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0
Mon,VZ Integration - CM,Reg,TB,Pending,PreProduction and CUT Releae of MHV 2016.4.0.18
Tue,SXV Cloud Integration - CM,Reg,TB,Pending,PreProduction and CUT Releae of MHV 2016.4.0.18
Wed,CMTr Cloud Integration - CM,Reg,TB,Pending,PreProduction and Emergency Releae of VZN 2016.4.0.18
;;;;
run;

Note the options used in the infile statement, and how the input statement is written.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 371

Re: Import csv file with column with long text

[ Edited ]

I am sorry, I thought that the log is long and the code is better.

 

By the way, I want to Status keep date formart as it is right now (11/28/2016)

 

What format should I put after Status?

 

I put mmddyy10. and it sitll not work.

 

The Log is below

 

Thanks.

 

HC

 

 

382
383 data want1;
384 infile 'C:\Users\HPNEW\Dropbox\VID ACCT\Original Files\Time Hour Report_sample.csv'
385 dlm=',' dsd truncover firstobs=2;
386 input DOW :$3. Task :$100. Type :$10. Approver :$10. Status mmddyy10. Description :$100.;
387 ;run;

NOTE: The infile 'C:\Users\HPNEW\Dropbox\VID ACCT\Original Files\Time Hour Report_sample.csv'
is:
Filename=C:\Users\HPNEW\Dropbox\VID ACCT\Original Files\Time Hour Report_sample.csv,
RECFM=V,LRECL=256,File Size (bytes)=2268,
Last Modified=24May2017:21:06:16,
Create Time=24May2017:20:50:44

NOTE: Invalid data for Status in line 6 43-52.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+
6 Wed,XTH - Subject Matter Expert II,Reg,EH,12/5/2016, 52
DOW=Wed Task=XTH - Subject Matter Expert II Type=Reg Approver=EH Status=. Description=
_ERROR_=1 _N_=5
NOTE: Invalid data for Status in line 7 43-52.
7 Thu,XTH - Subject Matter Expert II,Reg,EH,12/5/2016, 52
DOW=Thu Task=XTH - Subject Matter Expert II Type=Reg Approver=EH Status=. Description=
_ERROR_=1 _N_=6
NOTE: Invalid data for Status in line 8 43-52.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+
8 Fri,XTH - Subject Matter Expert II,Reg,EH,12/5/2016, 52
DOW=Fri Task=XTH - Subject Matter Expert II Type=Reg Approver=EH Status=. Description=
_ERROR_=1 _N_=7
NOTE: Invalid data for Status in line 29 21-30.
29 Sat,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 69
DOW=Sat Task=BL - I&T Type=Reg Approver=EH Status=.
Description=t/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 _ERROR_=1 _N_=28
NOTE: Invalid data for Status in line 30 21-30.
30 Sun,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 69
DOW=Sun Task=BL - I&T Type=Reg Approver=EH Status=.
Description=t/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 _ERROR_=1 _N_=29
NOTE: Invalid data for Status in line 31 21-30.
31 Mon,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 69
DOW=Mon Task=BL - I&T Type=Reg Approver=EH Status=.
Description=t/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 _ERROR_=1 _N_=30
NOTE: Invalid data for Status in line 32 21-30.
32 Tue,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 69
DOW=Tue Task=BL - I&T Type=Reg Approver=EH Status=.
Description=t/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 _ERROR_=1 _N_=31
NOTE: Invalid data for Status in line 33 21-30.
33 Wed,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 69
DOW=Wed Task=BL - I&T Type=Reg Approver=EH Status=.
Description=t/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 _ERROR_=1 _N_=32
NOTE: Invalid data for Status in line 34 21-30.
34 Thu,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 69
DOW=Thu Task=BL - I&T Type=Reg Approver=EH Status=.
Description=t/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 _ERROR_=1 _N_=33
NOTE: Invalid data for Status in line 35 21-30.
35 Fri,BL - I&T,Reg,EH,Pending,Sat/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 69
DOW=Fri Task=BL - I&T Type=Reg Approver=EH Status=.
Description=t/Sun MHV 2016.4.1.3 Tue MHV 2017.1.0.0 _ERROR_=1 _N_=34
NOTE: Invalid data for Status in line 36 32-41.
36 Mon,VZ Integration - CM,Reg,TB,Pending,PreProduction and CUT Releae of MHV 2016.4.0.1
86 8 86
DOW=Mon Task=VZ Integration - CM Type=Reg Approver=TB Status=.
Description=eProduction and CUT Releae of MHV 2016.4.0.18 _ERROR_=1 _N_=35
NOTE: Invalid data for Status in line 37 39-48.
37 Tue,SXV Cloud Integration - CM,Reg,TB,Pending,PreProduction and CUT Releae of MHV 201
86 6.4.0.18 93
DOW=Tue Task=SXV Cloud Integration - CM Type=Reg Approver=TB Status=.
Description=eProduction and CUT Releae of MHV 2016.4.0.18 _ERROR_=1 _N_=36
NOTE: Invalid data for Status in line 38 40-49.
38 Wed,CMTr Cloud Integration - CM,Reg,TB,Pending,PreProduction and Emergency Releae of
86 VZN 2016.4.0.18 100
DOW=Wed Task=CMTr Cloud Integration - CM Type=Reg Approver=TB Status=.
Description=eProduction and Emergency Releae of VZN 2016.4.0.18 _ERROR_=1 _N_=37
NOTE: 37 records were read from the infile 'C:\Users\HPNEW\Dropbox\VID ACCT\Original
Files\Time Hour Report_sample.csv'.
The minimum record length was 23.
The maximum record length was 100.
NOTE: The data set WORK.WANT1 has 37 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.04 seconds

 

Super User
Super User
Posts: 6,500

Re: Import csv file with column with long text

You are getting errors about invalid STATUS because you used an informat without adding the : modifier.

input DOW :$3. Task :$100. Type :$10. Approver :$10. Status mmddyy10. Description :$100.;

So when the actual data value is less than 10 characters like this record:

Wed,XTH - Subject Matter Expert II,Reg,EH,12/5/2016, 52

SAS will try to read the comma.  Adding the colon modifer tells SAS to use the actual width of the data instead of the width specified in the INFORMAT.

input DOW :$3. Task :$100. Type :$10. Approver :$10. Status :mmddyy10. Description :$100.;

To have SAS display the date in 'MM/DD/YYYY' style you need to use the MMDDYYS10. format.

Super Contributor
Posts: 371

Re: Import csv file with column with long text

Thank you all so much for your help.

HC

Super User
Posts: 10,500

Re: Import csv file with column with long text


hhchenfx wrote:

I meant the first several record has the "note" value blank.

And I have to keep all record.

 

HC


Use the Guessingrows option to tell proc import to look at more than the first few rows of a dataset. Add Something like

Guessingrows = 30000;

to the import code.

Solution
‎05-25-2017 12:10 PM
PROC Star
Posts: 7,363

Re: Import csv file with column with long text

You left off the colon before Status in your input statement. However that, in itself, won't correct all of the problems. Some of your dates have values of Pending. If a missing value of .P will suffice for those records, the following should work for you:

data want1 (drop=_:);
  infile '/folders/myfolders/Time Hour Report_sample.csv'
    dlm=',' dsd truncover firstobs=2;
  input DOW :$3. Task :$100. Type :$10. Approver :$10. _check :$10. Description :$100.;
  if anydigit(first(_check)) then Status=input(_check,mmddyy10.);
  else Status=.P;
  format status mmddyys10.;
run;

Art, CEO, AnalystFinder.com

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 178 views
  • 1 like
  • 7 in conversation