topic Re: Data importing step in New SAS User

Data importing step

Sandeep77 — Thu, 23 Mar 2023 09:02:33 GMT

Hi all,

I am importing a CSV file from a location to SAS. When I am importing the data is imported and I can see the output results as normal but in the log it shows an error as Import unsuccessful. Can you please suggest the error that I am doing in importing the file?

Proc import 
datafile='\\lowell2.local\shares\Public\Trace\Trace Integrity Analyst Data\BAU_Processes\SAS\Enhanced TPT\Files To Send\EQ_Trace_2023_02_10_10FEB2023.CSV'
out=Feb_release
dbms=csv
replace;
run;

Log:
     

Errors detected in submitted DATA step. Examine log.
995394 rows created in WORK.FEB_RELEASE from \\lowell2.local\shares\Public\Trace\Trace Integrity Analyst 
Data\BAU_Processes\SAS\Enhanced TPT\Files To Send\EQ_Trace_2023_02_10_10FEB2023.CSV.
  
  
  
ERROR: Import unsuccessful.  See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           2.60 seconds
      user cpu time       1.48 seconds
      system cpu time     0.07 seconds
      memory              8646.65k
      OS Memory           39020.00k
      Timestamp           03/23/2023 09:01:20 AM
      Step Count                        60  Switch Count  11

Re: Data importing step

yabwon — Thu, 23 Mar 2023 09:15:30 GMT

You are showing proc import code, and the log says:

Errors detected in submitted DATA step.

I think it is not all the log information you are sharing.

Bart

Re: Data importing step

Kurt_Bremser — Thu, 23 Mar 2023 09:38:40 GMT

Never use PROC IMPORT for text files. Write the DATA step yourself, according to the documentation of the csv file. Guarantees consistent and correct results, which the guessing of PROC IMPORT can't.

Re: Data importing step

Sandeep77 — Thu, 23 Mar 2023 10:08:56 GMT

I used the data step to import the file as you suggested and it ran without any errors but the dataset is blank. It is showing only the number of rows but no data in the dataset.

data EQ_Trace_2023_02_10_10FEB2023; 
infile '\\lowell2.local\shares\Public\Trace\Trace Integrity Analyst Data\BAU_Processes\SAS\Enhanced TPT\Storage\Monthly Washes\Trace_2023_02_10\elnz_Lowell_10Feb.CSV';
input ;
run;
Log:
1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='S:\Trace\Trace Integrity Analyst Data\Projects and Tasks\3. Sandeep\Equifax released.egp';
6          %LET _CLIENTPROJECTPATHHOST='LWLT5CG9322XFL';
7          %LET _CLIENTPROJECTNAME='Equifax released.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         ODS LISTING GPATH=&sasworklocation;
18         FILENAME EGHTML TEMP;
19         ODS HTML5(ID=EGHTML) FILE=EGHTML
20             OPTIONS(BITMAP_MODE='INLINE')
21             %HTML5AccessibleGraphSupported
22             ENCODING='utf-8'
23             STYLE=HTMLBlue
24             NOGTITLE
25             NOGFOOTNOTE
26             GPATH=&sasworklocation
27         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28         
29         data EQ_Trace_2023_02_10_10FEB2023;
30         infile '\\lowell2.local\shares\Public\Trace\Trace Integrity Analyst Data\BAU_Processes\SAS\Enhanced TPT\Storage\Monthly
30       ! Washes\Trace_2023_02_10\elnz_Lowell_10Feb.CSV';
31         input ;
32         run;

NOTE: Compression was disabled for data set WORK.EQ_TRACE_2023_02_10_10FEB2023 because compression overhead would increase the size 
      of the data set.
NOTE: The infile '\\lowell2.local\shares\Public\Trace\Trace Integrity Analyst Data\BAU_Processes\SAS\Enhanced TPT\Storage\Monthly 
      Washes\Trace_2023_02_10\elnz_Lowell_10Feb.CSV' is:
      
      Filename=\\lowell2.local\shares\Public\Trace\Trace Integrity Analyst Data\BAU_Processes\SAS\Enhanced TPT\Storage\Monthly 
      Washes\Trace_2023_02_10\elnz_Lowell_10Feb.CSV,
      RECFM=V,LRECL=32767,
      File Size (bytes)=263495880,
      Last Modified=15Feb2023:10:23:24,
      Create Time=15Feb2023:10:27:08

NOTE: 995395 records were read from the infile '\\lowell2.local\shares\Public\Trace\Trace Integrity Analyst 
      Data\BAU_Processes\SAS\Enhanced TPT\Storage\Monthly Washes\Trace_2023_02_10\elnz_Lowell_10Feb.CSV'.
      The minimum record length was 114.
      The maximum record length was 1345.
NOTE: The data set WORK.EQ_TRACE_2023_02_10_10FEB2023 has 995395 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           1.34 seconds
      user cpu time       0.48 seconds
      system cpu time     0.06 seconds
      memory              749.06k
      OS Memory           35428.00k
2                                                          The SAS System                            10:50 Wednesday, March 22, 2023

      Timestamp           03/23/2023 10:07:12 AM
      Step Count                        72  Switch Count  2
      

33         
34         %LET _CLIENTTASKLABEL=;
35         %LET _CLIENTPROCESSFLOWNAME=;
36         %LET _CLIENTPROJECTPATH=;
37         %LET _CLIENTPROJECTPATHHOST=;
38         %LET _CLIENTPROJECTNAME=;
39         %LET _SASPROGRAMFILE=;
40         %LET _SASPROGRAMFILEHOST=;
41         
42         ;*';*";*/;quit;run;
43         ODS _ALL_ CLOSE;
44         
45         
46         QUIT; RUN;
47

Re: Data importing step

Kirito1 — Thu, 23 Mar 2023 10:43:28 GMT

Try importing the data with the infile method. As below code.

DATA DWH4;
INFILE 'address of the file with its extension like (.csv)'
missover
dsd
firstobs=2;
/* $ = here represents the length of the data8/
/*for example a mobile number has should be of length 10 then it would be $10.*/
informat 'column1 name'n $100.;
informat 'column2 name'n $100.;


format 'column1 name'n $100.;
format 'column2 name'n $100.;


input

'Column1 name'n $
'Column2 name'n $;
run;

It gives us more flexibility. Although, Importing is the best procedure but it sometimes creates problem. So what I did is to use infile method for a very large data.

Re: Data importing step

Ksharp — Thu, 23 Mar 2023 11:46:56 GMT

/*
Try guessingrows=max and getnames=no options
*/
Proc import
datafile='\\lowell2.local\shares\Public\Trace\Trace Integrity Analyst Data\BAU_Processes\SAS\Enhanced TPT\Files To Send\EQ_Trace_2023_02_10_10FEB2023.CSV'
out=Feb_release
dbms=csv
replace;
guessingrows=max;
getnames=no ;
run;

Re: Data importing step

Sandeep77 — Thu, 23 Mar 2023 12:15:54 GMT

The code ran successfully but it only pulled out 11 columns out of 72 and all the column headers were changed to VAR1, VAR2... to VAR11.

Re: Data importing step

Kurt_Bremser — Thu, 23 Mar 2023 12:17:39 GMT

Never make your life unnecessary hard by using those stupid name literals. Use valid V7 SAS names.

Re: Data importing step

Kurt_Bremser — Thu, 23 Mar 2023 12:22:47 GMT

With an empty INPUT statement, no variables will be created/populated.

You MUST (as I already said) write the DATA step according to the documentation of the file, where all variables and their attributes will be described.

If you did not receive documentation, return to sender, or pay a visit to them wielding a suitable instrument, like a baseball bat.

In case some pumpkin sent you the file without doc, we might be able to make some educated guesses when we see the file's contents. Open it with a text editor (NOT with Excel!!!) and copy/paste the first few lines into a window opened with this button:

Re: Data importing step

Ksharp — Thu, 23 Mar 2023 12:29:34 GMT

So I suggested you post some your real data(not all) to test your code and find out where is problem.

Re: Data importing step

ballardw — Thu, 23 Mar 2023 15:02:46 GMT

Copy the first 5 lines from the file. Open your file with a TEXT EDITOR to copy the text. On the forum open a text box with the </> and paste the result.

IF the content is sensitive AFTER pasting into the text box type over sensitive values with XXX (character) or 000 (numeric).

One suspects the file is not actually a simple CSV.