BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Barite | Level 11

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; 
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

13 REPLIES 13
Patrick
Opal | Level 21

@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...

 

art297
Opal | Level 21

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

 

Reeza
Super User

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..

 

 

hhchenfx
Barite | Level 11

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

And I have to keep all record.

 

HC

hhchenfx
Barite | Level 11

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
Kurt_Bremser
Super User

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.

hhchenfx
Barite | Level 11

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

Kurt_Bremser
Super User

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.

hhchenfx
Barite | Level 11

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

 

Tom
Super User Tom
Super User

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.

hhchenfx
Barite | Level 11

Thank you all so much for your help.

HC

ballardw
Super User

@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.

art297
Opal | Level 21

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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