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 $
-
22
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
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)
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.
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
Your code HAS an @ that is the issue. Check the line above the ERROR. Both logs, same place.
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
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):
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.
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.