topic Re: Importing issue in csv file in SAS Programming

Importing issue in csv file

Ravikumarpa4 — Wed, 08 Mar 2017 05:45:53 GMT

Hi tried to import attached csv file i'm able to import data but its not comping in proper way attached input csv and output csv file

log

1 data WORK.CUSTOMER ;

2 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

3 infile 'C:\Users\Training5\Desktop\New folder\Customers.csv' delimiter = ',' MISSOVER DSD

3 ! lrecl=32767 firstobs=2 ;

4 informat CustomerID $4.;

5 informat CompanyName $28. ;

6 informat ContactName $28. ;

7 informat City $16. ;

8 informat Country $12. ;

9 informat DivisionID best32. ;

10 informat Address $50. ;

11 informat Fax $18. ;

12 informat Phone $18. ;

13 informat PostalCode $10. ;

14 informat StateProvince $2. ;

15 format CustomerID $4. ;

16 format CompanyName $28. ;

17 format ContactName $28. ;

18 format City $16. ;

19 format Country $12. ;

20 format DivisionID best12. ;

21 format Address $50. ;

22 format Fax $18. ;

23 format Phone $18. ;

24 format PostalCode $10. ;

25 format StateProvince $2. ;

26 input

27 CustomerID $

28 CompanyName $

29 ContactName $

30 City $

31 Country $

32 DivisionID

33 Address $@

34 Fax $

ERROR 22-322: Expecting a name.

35 Phone $

36 PostalCode $

37 StateProvince $

38 ;

39 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

40 run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.CUSTOMER may be incomplete. When this step was stopped there were 0

observations and 11 variables.

NOTE: DATA statement used (Total process time):

real time 0.19 seconds

cpu time 0.01 seconds

41 data WORK.CUSTOMER ;

42 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

43 infile 'C:\Users\Training5\Desktop\New folder\Customers.csv' delimiter = ',' MISSOVER DSD

43 ! lrecl=32767 firstobs=2 ;

44 informat CustomerID $4.;

45 informat CompanyName $28. ;

46 informat ContactName $28. ;

47 informat City $16. ;

48 informat Country $12. ;

49 informat DivisionID best32. ;

50 informat Address $50. ;

51 informat Fax $18. ;

52 informat Phone $18. ;

53 informat PostalCode $10. ;

54 informat StateProvince $2. ;

55 format CustomerID $4. ;

56 format CompanyName $28. ;

57 format ContactName $28. ;

58 format City $16. ;

59 format Country $12. ;

60 format DivisionID best12. ;

61 format Address $50. ;

62 format Fax $18. ;

63 format Phone $18. ;

64 format PostalCode $10. ;

65 format StateProvince $2. ;

66 input

67 CustomerID $

68 CompanyName $

69 ContactName $

70 City $

71 Country $

72 DivisionID

73 Address $

74 Fax $

75 Phone $

76 PostalCode $

77 StateProvince $

78 ;

79 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

80 run;

NOTE: The infile 'C:\Users\Training5\Desktop\New folder\Customers.csv' is:

Filename=C:\Users\Training5\Desktop\New folder\Customers.csv,

RECFM=V,LRECL=32767,File Size (bytes)=9630,

Last Modified=07Mar2017:07:29:42,

Create Time=07Mar2017:07:29:37

NOTE: 100 records were read from the infile 'C:\Users\Training5\Desktop\New folder\Customers.csv'.

The minimum record length was 33.

The maximum record length was 135.

NOTE: The data set WORK.CUSTOMER has 100 observations and 11 variables.

NOTE: DATA statement used (Total process time):

real time 0.10 seconds

cpu time 0.04 seconds

Re: Importing issue in csv file

Kurt_Bremser — Wed, 08 Mar 2017 05:56:55 GMT

Please post log snippets using the {i} button, so that the formatting is preserved.

You have a stray @ in your input statement causing an error (very obvious). Fix that first and try again.

Re: Importing issue in csv file

Ravikumarpa4 — Wed, 08 Mar 2017 06:13:40 GMT

now i tried with @

data WORK.CUSTOMER ;

%let _EFIERR_ = 0; /* set the ERROR detection macro variable */

infile 'C:\Users\Training5\Desktop\New folder\Customers.csv' delimiter = ',' dsd MISSOVER lrecl=32767 firstobs=2 ;

informat CustomerID $4.;

informat CompanyName $28. ;

informat ContactName $28. ;

informat City $16. ;

informat Country $12. ;

informat DivisionID best32. ;

informat Address $50.;

informat Fax $18. ;

informat Phone $18. ;

informat PostalCode $10. ;

informat StateProvince $2. ;

format CustomerID $4. ;

format CompanyName $28. ;

format ContactName $28. ;

format City $16. ;

format Country $12. ;

format DivisionID best12. ;

format Address $50. ;

format Fax $18. ;

format Phone $18. ;

format PostalCode $10. ;

format StateProvince $2. ;

input

CustomerID $

CompanyName $

ContactName $

City $

Country $

DivisionID

Address $ @

Fax $

Phone $

PostalCode $

StateProvince $

;

if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

run;

log

653  data WORK.CUSTOMER    ;
654  %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
655  infile 'C:\Users\Training5\Desktop\New folder\Customers.csv' delimiter = ',' dsd MISSOVER
655! lrecl=32767 firstobs=2 ;
656  informat CustomerID $4.;
657  informat CompanyName $28. ;
658  informat ContactName $28. ;
659  informat City $16. ;
660  informat Country $12. ;
661  informat DivisionID best32. ;
662  informat Address $50.;
663  informat Fax $18. ;
664  informat Phone $18. ;
665  informat PostalCode $10. ;
666  informat StateProvince $2. ;
667  format CustomerID $4. ;
668  format CompanyName $28. ;
669  format ContactName $28. ;
670  format City $16. ;
671  format Country $12. ;
672  format DivisionID best12. ;
673  format Address $50. ;
674  format Fax $18. ;
675  format Phone $18. ;
676  format PostalCode $10. ;
677  format StateProvince $2. ;
678  input
679  CustomerID $
680  CompanyName $
681  ContactName $
682  City $
683  Country $
684  DivisionID
685  Address $ @
686  Fax $
         -
         22
ERROR 22-322: Expecting a name.

687  Phone $
688  PostalCode $
689  StateProvince $
690  ;
691  if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
692  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CUSTOMER may be incomplete.  When this step was stopped there were 0
         observations and 11 variables.
WARNING: Data set WORK.CUSTOMER was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: Importing issue in csv file

Reeza — Wed, 08 Mar 2017 06:16:16 GMT

Your code HAS an @ that is the issue. Check the line above the ERROR. Both logs, same place.

Re: Importing issue in csv file

Ravikumarpa4 — Wed, 08 Mar 2017 06:29:06 GMT

819  data WORK.CUSTOMER    ;
820  %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
821  infile 'C:\Users\Training5\Desktop\New folder\Customers.csv' delimiter = ',' dsd MISSOVER
821! lrecl=32767 firstobs=2 ;
822  informat CustomerID $4.;
823  informat CompanyName $28. ;
824  informat ContactName $28. ;
825  informat City $16. ;
826  informat Country $12. ;
827  informat DivisionID best32. ;
828  informat Address $50.;
829  informat Fax $18. ;
830  informat Phone $18. ;
831  informat PostalCode $10. ;
832  informat StateProvince $2. ;
833  format CustomerID $4. ;
834  format CompanyName $28. ;
835  format ContactName $28. ;
836  format City $16. ;
837  format Country $12. ;
838  format DivisionID best12. ;
839  format Address $50. ;
840  format Fax $18. ;
841  format Phone $18. ;
842  format PostalCode $10. ;
843  format StateProvince $2. ;
844  input
845  CustomerID $
846  CompanyName $
847  ContactName $
848  City $
849  Country $
850  DivisionID
851  Address $
852  Fax $
853  Phone $
854  PostalCode $
855  StateProvince $
856  ;
857  if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
858  run;

NOTE: The infile 'C:\Users\Training5\Desktop\New folder\Customers.csv' is:
      Filename=C:\Users\Training5\Desktop\New folder\Customers.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=9630,
      Last Modified=07Mar2017:07:29:42,
      Create Time=07Mar2017:07:29:37

NOTE: 100 records were read from the infile 'C:\Users\Training5\Desktop\New folder\Customers.csv'.
      The minimum record length was 33.
      The maximum record length was 135.
NOTE: The data set WORK.CUSTOMER has 100 observations and 11 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
but output not coming in proper way

Re: Importing issue in csv file

Patrick — Wed, 08 Mar 2017 06:38:20 GMT

For your 2nd data step which executes without errors:

1. remove the $ in the Input statement. You're already defining in your Informat statements how text needs to be read into the SAS variables.

2. add TERMSTR=CRLF to your infile options

The reason why your data gets messed up: You're having a linefeed LF in your data which SAS interpretes as an end of line indicator. Adding option TERMSTR=CRLF instructs SAS to only interprete CRLF as end of line indicator.

A quick way to inspect source files for such issues is to use a tool like Notepad++ showing all characters (set under: View/Show Symbol/Show all Characters)

Re: Importing issue in csv file

Patrick — Wed, 08 Mar 2017 06:59:09 GMT

@Ravikumarpa4

What you probably also want to do once you've read your data into variables is to run a translate() function or the like to replace the LF with a blank or a comma. If you don't do this then some Proc's will print your data as below (Proc Print example):

Re: Importing issue in csv file

Kurt_Bremser — Wed, 08 Mar 2017 07:50:58 GMT

Just to make it clear what the stray @ caused:

685  Address $ @
686  Fax $
         -
         22
ERROR 22-322: Expecting a name.

The "@" is interpreted as the start of a position marker, so the next item in the code should designate a position; since this can be done with a number literal or a variable, "Fax" is interpreted as this position variable. To continue in a syntactically correct way, SAS now expects the name of a variable to be read. So the "$" comes unexpected and triggers the syntax error.