topic Re: import error for csv file using proc import in sas on demand in SAS Programming

import error for csv file using proc import in sas on demand

BrahmanandaRao — Tue, 25 Apr 2023 18:35:44 GMT

proc import datafile='/home/u35263349/Product Sales_Candidate Attach 1_PresSE_013.csv'
out=ff.product_sales
dbms=csv;
delimiter=',';
guessingrows=max;
run;

1. I am struggling above error in sas on demand studio please find below csv attachment file

Re: import error for csv file using proc import in sas on demand

PaigeMiller — Tue, 25 Apr 2023 18:38:06 GMT

Do you see where it says "no data in first 5 records"? Please investigate that.

Re: import error for csv file using proc import in sas on demand

BrahmanandaRao — Tue, 25 Apr 2023 18:41:47 GMT

i open and check csv checked but shows
from first row how to solve this problem please do needful

Re: import error for csv file using proc import in sas on demand

PaigeMiller — Tue, 25 Apr 2023 19:09:25 GMT

As I recall, when you run PROC IMPORT on a CSV file, SAS creates code and executes it and this code and other error messages appears in the log. But you don't show us that code in the log. Show us.

Re: import error for csv file using proc import in sas on demand

BrahmanandaRao — Tue, 25 Apr 2023 19:16:29 GMT

I put log error snap shot

Re: import error for csv file using proc import in sas on demand

PaigeMiller — Tue, 25 Apr 2023 19:19:54 GMT

That's it, no additional parts of the log?

Re: import error for csv file using proc import in sas on demand

ballardw — Tue, 25 Apr 2023 19:38:31 GMT

The file appears to be using a Carriage Return character to end the lines in the file. I think from your syntax that you are running your code in SAS On Demand which is a Linux implementation and expects either a Line Feed or can use the Windows CRLF line terminator.

Try

filename in  "/home/u35263349/Product Sales_Candidate Attach 1_PresSE_013.csv" Termstr=CR;

proc import datafile=in
out=product_sales
dbms=csv;
delimiter=',';
guessingrows=max;
run;

Proc Import doesn't support the Termstr= option directly but the Filename does. So you can add that option as above.

Re: import error for csv file using proc import in sas on demand

Tom — Tue, 25 Apr 2023 19:44:49 GMT

Your file appears to be using a bare CR as the end of line marker.

NOTE: The infile CSV is:
      (system-specific pathname),
      (system-specific file attributes)

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1         Rest Owner,Rest Coop,REST_KEY,MITM_KEY,Menu Item,Menu Item Combo Meal Flag,Reporting Day,POS Consume
     101  r Price,POS Total Units Sold  Promo and Regular,POS Promotion Units Sold,POS Units Sold,POS Combo Un

     201  its Sold.SMITH JOE,SEA/TCA WA CP-0024,1364,1,00000001-REUBEN,N,1/1/11,0.95,25,0,25,6.SMITH JOE,SEA/T
    ZONE  6772566605445424442544254425424523333233332323333333325454442423232332323323323233230544542444254425
    NUMR  94303FC4D3D9480AF5C351F431071030D0024C1364C1C00000001D25525ECEC1F1F11C0E95C25C0C25C6D3D9480AF5C351F4

So make sure to tell SAS that before trying to use PROC IMPORT to GUESS how to read the file.

filename csv "C:\downloads\Product Sales_Candidate Attach 1_PresSE_013.csv" 
  termstr=cr
;
proc import file=csv dbms=csv out=want replace;
run;

NOTE: 6516 records were read from the infile (system-specific pathname).
      The minimum record length was 72.
      The maximum record length was 94.
NOTE: The data set WORK.WANT has 6516 observations and 12 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


6516 rows created in WORK.WANT from CSV.

Note that there is still a bug in Excel on a Mac where it does not understand that MacOS is now a flavor of Unix and it should use LF as the end of line marker. If you want to convert a spreadsheet to a CSV file on a Mac make sure to select the right file format so that it makes a file that use LF or CRLF as the end of line marker.

Re: import error for csv file using proc import in sas on demand

BrahmanandaRao — Wed, 26 Apr 2023 04:40:44 GMT

Thank you vey much for your solution now i can import file succefully
but in that file variable have embadded blanks
in log it shows error even if validname=v7 option

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
68
69 proc sql;
70 select * from product_sales_ds
71 where POS Consumer Price=0;
________
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE,
NET, NOT, NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
72 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 276.90k
OS Memory 27540.00k
Timestamp 26/04/2023 04:39:55 AM
Step Count 414 Switch Count 0
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

73
74
75 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
85

proc sql;
select * from product_sales_ds
where POS Consumer Price=0;
quit;

Re: import error for csv file using proc import in sas on demand

Kurt_Bremser — Wed, 26 Apr 2023 05:15:14 GMT

A SELECT statement in SQL must contain a FROM clause.

Re: import error for csv file using proc import in sas on demand

BrahmanandaRao — Wed, 26 Apr 2023 05:48:25 GMT

i have embadded blanks for all below vairables how to add uderscore between embadded blanks even if i used options validvarname=v7 option and proc import
please give fix this problem

Re: import error for csv file using proc import in sas on demand

Kurt_Bremser — Wed, 26 Apr 2023 05:51:51 GMT

Do NOT use PROC IMPORT for text files, write your own data step where you have full control over all names and attributes.

Re: import error for csv file using proc import in sas on demand

BrahmanandaRao — Wed, 26 Apr 2023 05:52:58 GMT

it is csv file only

Re: import error for csv file using proc import in sas on demand

Kurt_Bremser — Wed, 26 Apr 2023 05:58:34 GMT

@BrahmanandaRao wrote:
it is csv file only

Which you read with a DATA step, period.

Re: import error for csv file using proc import in sas on demand

BrahmanandaRao — Wed, 26 Apr 2023 06:04:00 GMT

options validvarname = V7;
filename psales "/home/u35263349/Product Sales_Candidate Attach 1_PresSE_013.csv"  termstr=cr;


proc import file=psales dbms=csv out=product_sales_ds replace;
run;

issue is blanks within variables names please find attachment

Re: import error for csv file using proc import in sas on demand

Kurt_Bremser — Wed, 26 Apr 2023 06:09:04 GMT

Please READ my posts. Do NOT use PROC IMPORT.

@BrahmanandaRao wrote:
options validvarname = V7;
filename psales "/home/u35263349/Product Sales_Candidate Attach 1_PresSE_013.csv"  termstr=cr;


proc import file=psales dbms=csv out=product_sales_ds replace;
run;
issue is blanks within variables names please find attachment

Re: import error for csv file using proc import in sas on demand

BrahmanandaRao — Wed, 26 Apr 2023 06:36:27 GMT

pleas provide link

Re: import error for csv file using proc import in sas on demand

Kurt_Bremser — Wed, 26 Apr 2023 06:41:16 GMT

@BrahmanandaRao wrote:
pleas provide link

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/pgmsashome/home.htm, section Data Step Programming.

Re: import error for csv file using proc import in sas on demand

Kurt_Bremser — Wed, 26 Apr 2023 12:03:24 GMT

Use these options in your INFILE statement:

termstr=CR
dlm=","
firstobs=2
dsd
truncover

This file was quite obviously created on a Mac.

Re: import error for csv file using proc import in sas on demand

Tom — Wed, 26 Apr 2023 16:07:59 GMT

You need to use an actual variable name in your code, not gibberish like this:

where POS Consumer Price=0

That code has three names POS, Consumer, and Price without any operators between them.

If you use PROC IMPORT with VALIDVARNAME option set to V7 then a COLUMN with a header of 'POS Consumer Price' will become a VARIABLE named POS_Consumer_Price.

So use

where POS_Consumer_Price=0

in the code.

If you accidentally ran the PROC IMPORT with VALIDVARNAME set to ALL then you will have to use a name literal in your code.

where 'POS Consumer Price'n=0