Importing Excel to SAS
Solved
grhelene
Posted messages
6
Status
Member
-
max75010 -
max75010 -
Hello,
I would like to import a table from Excel to SAS. I can do that, but I have a variable that contains commas, which disturbs SAS. It imports the commas but not in the correct position in the number, e.g.:
Excel SAS
0.50 50
1.11 111
13.36 1,336
I have already tried changing the format in Excel, going through a .txt file, etc., to no avail.
If someone can help me, that would be very kind.
Thank you
I would like to import a table from Excel to SAS. I can do that, but I have a variable that contains commas, which disturbs SAS. It imports the commas but not in the correct position in the number, e.g.:
Excel SAS
0.50 50
1.11 111
13.36 1,336
I have already tried changing the format in Excel, going through a .txt file, etc., to no avail.
If someone can help me, that would be very kind.
Thank you
Configuration: Windows 2000 Internet Explorer 6.0
23 answers
- 1
- 2
Next
Hello,
Regarding Excel link errors, aside from some issues already mentioned by other users above, in my case, this is due to the absence of the module "SAS access to pc files". To check if you have it, you need to run the following instruction in a program sheet:
proc setinit;
run;
This program lists all the modules you are renting.
Since I do not have this module, I use the following instruction to import an Excel table, although it is quite cumbersome:
data x.toto;
infile 'Z:\Etudes\Orientation\table_sas_X2\tutu_2004_partie1.csv' dlm=';' dsd truncover firstobs=2;
/*the access path where your Excel file is located, previously saved in CSV format with ';' as the field delimiter*/
input /*instruction that builds your SAS table. each variable is named and its length and type defined, in the order they appear in your CSV file. This assumes having checked the content (numeric or alpha) and the length of each of your variables beforehand (to avoid truncations)*/
ident:4.
sexe:$1.
MN:$25.
AN:4.
DS2:$25.
GRAGE:$1.
CS2:$1.
CS23:$1.
Q1:$1.
Q1_8:$25.
Q2m:$2.
Q2j:$2.
Q2_nsp:$4.
Q2_NR:$1.
Q3:2.
Q4:$1.
Q5:$1.
Q6:$1.
Q7:$1.
Q73:$2.;
run;
Good luck!
Regarding Excel link errors, aside from some issues already mentioned by other users above, in my case, this is due to the absence of the module "SAS access to pc files". To check if you have it, you need to run the following instruction in a program sheet:
proc setinit;
run;
This program lists all the modules you are renting.
Since I do not have this module, I use the following instruction to import an Excel table, although it is quite cumbersome:
data x.toto;
infile 'Z:\Etudes\Orientation\table_sas_X2\tutu_2004_partie1.csv' dlm=';' dsd truncover firstobs=2;
/*the access path where your Excel file is located, previously saved in CSV format with ';' as the field delimiter*/
input /*instruction that builds your SAS table. each variable is named and its length and type defined, in the order they appear in your CSV file. This assumes having checked the content (numeric or alpha) and the length of each of your variables beforehand (to avoid truncations)*/
ident:4.
sexe:$1.
MN:$25.
AN:4.
DS2:$25.
GRAGE:$1.
CS2:$1.
CS23:$1.
Q1:$1.
Q1_8:$25.
Q2m:$2.
Q2j:$2.
Q2_nsp:$4.
Q2_NR:$1.
Q3:2.
Q4:$1.
Q5:$1.
Q6:$1.
Q7:$1.
Q73:$2.;
run;
Good luck!
Hello,
How do you import your Excel files?
Try this:
-> Convert your source Excel files to CSV
-> Adapt this code:
proc import datafile="file path\file name.csv"
out=mydata
dbms=dlm
replace;
delimiter=';'; /* CSV delimited by ';' try ',' if it doesn't work */
getnames=yes;
run;
Good luck,
Steel
--
- Did you see Chloraine?
- Who's Chloraine? - Ah Ah Ah!
How do you import your Excel files?
Try this:
-> Convert your source Excel files to CSV
-> Adapt this code:
proc import datafile="file path\file name.csv"
out=mydata
dbms=dlm
replace;
delimiter=';'; /* CSV delimited by ';' try ',' if it doesn't work */
getnames=yes;
run;
Good luck,
Steel
--
- Did you see Chloraine?
- Who's Chloraine? - Ah Ah Ah!
Hello,
Thank you very much for your response.
Actually, I was directly importing my xls file into SAS.
I applied your suggestion by converting my xls file to csv. It seems that it manages to read something (it's not quite there yet, but at least it indicates that there is data in the column). Now, I just need to find out how to tell it to read the data it retrieves ^-^.
In any case, thank you very much for your help.
Thank you very much for your response.
Actually, I was directly importing my xls file into SAS.
I applied your suggestion by converting my xls file to csv. It seems that it manages to read something (it's not quite there yet, but at least it indicates that there is data in the column). Now, I just need to find out how to tell it to read the data it retrieves ^-^.
In any case, thank you very much for your help.
Hello,
I see that everyone here is using the import proc.
Personally, I don't like it at all and many people advise using csv files, which is a good start. Even better if we use an INFILE.
If, like me, you prefer to use the Excel file in its original state (God knows it's tedious to have to rework the source files one by one), I recommend DDE links.
Basically, the SAS program will open Excel and retrieve all the cells that you have instructed it to fetch in the format you have chosen. Here is an example:
options noxwait noxsync;
x 'D:\excel.exe'; /* THE ACCESS PATH MUST BE MODIFIED */
data _null_;
sleep = mcipislp(3);
run;
filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[open("D:\PT_CODE.xls")]'; /* Indicate the complete path and the name of the file */
run;
filename test dde 'excel|Fusion_crash ! r2c1:r38c3' lrecl=2000;
/* Fusion_crash to be modified by the name of the sheet
r2c1:r38c3: the range of cells to be retrieved here 36 rows and 3 columns
Lrecl if omitted defaults to 255 modify if the length of the record is insufficient */
data z.missing_codes; /* Name of the table */
attrib
Old_Country_name length=$250 /* Assign the variables and their length */
Country_name length=$250
Country_cd length=$3
;
infile test dlm='09'x notab dsd truncover pad;
input
Old_Country_name $
Country_name $
Country_cd $
;
run;
filename cmds dde 'excel|system';
data _null_; /* To close Excel */
file cmds;
put '[quit()]';
run;
It is quite possible to turn it into a macro to import multiple files.
The puts allow controlling Excel via SAS using X4ML (Excel 4) language and allow automating the reformatting of sheets if the cells are merged, for example.
If needed, you can ask for a loop import macro.
Good luck!
I see that everyone here is using the import proc.
Personally, I don't like it at all and many people advise using csv files, which is a good start. Even better if we use an INFILE.
If, like me, you prefer to use the Excel file in its original state (God knows it's tedious to have to rework the source files one by one), I recommend DDE links.
Basically, the SAS program will open Excel and retrieve all the cells that you have instructed it to fetch in the format you have chosen. Here is an example:
options noxwait noxsync;
x 'D:\excel.exe'; /* THE ACCESS PATH MUST BE MODIFIED */
data _null_;
sleep = mcipislp(3);
run;
filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[open("D:\PT_CODE.xls")]'; /* Indicate the complete path and the name of the file */
run;
filename test dde 'excel|Fusion_crash ! r2c1:r38c3' lrecl=2000;
/* Fusion_crash to be modified by the name of the sheet
r2c1:r38c3: the range of cells to be retrieved here 36 rows and 3 columns
Lrecl if omitted defaults to 255 modify if the length of the record is insufficient */
data z.missing_codes; /* Name of the table */
attrib
Old_Country_name length=$250 /* Assign the variables and their length */
Country_name length=$250
Country_cd length=$3
;
infile test dlm='09'x notab dsd truncover pad;
input
Old_Country_name $
Country_name $
Country_cd $
;
run;
filename cmds dde 'excel|system';
data _null_; /* To close Excel */
file cmds;
put '[quit()]';
run;
It is quite possible to turn it into a macro to import multiple files.
The puts allow controlling Excel via SAS using X4ML (Excel 4) language and allow automating the reformatting of sheets if the cells are merged, for example.
If needed, you can ask for a loop import macro.
Good luck!
Thank you for your response, but I can't save as .csv with the ' " ' option but only with the ';' option.
Hello,
I am currently programming statistical queries in SAS from source files in EXCEL... (I am a beginner in SAS and programming)
My issue is that when I import my EXCEL file into SAS, all the fields I want to retrieve are, except for the column where the dates are located.
I did 2 tests with two source files built on the same model but for different periods.
- file 1: no problem, I retrieve everything correctly.
- file 2: the dates are not retrieved at all in SAS.
For both files, the column starts with empty cells (=> for SAS the cell content = text => further processing to convert the text into SAS date...)
I do not understand why in one case, before processing the file, I retrieve my data and in the other SAS considers the column empty...
Could you please help me?
Thank you in advance.
I am currently programming statistical queries in SAS from source files in EXCEL... (I am a beginner in SAS and programming)
My issue is that when I import my EXCEL file into SAS, all the fields I want to retrieve are, except for the column where the dates are located.
I did 2 tests with two source files built on the same model but for different periods.
- file 1: no problem, I retrieve everything correctly.
- file 2: the dates are not retrieved at all in SAS.
For both files, the column starts with empty cells (=> for SAS the cell content = text => further processing to convert the text into SAS date...)
I do not understand why in one case, before processing the file, I retrieve my data and in the other SAS considers the column empty...
Could you please help me?
Thank you in advance.
Hello,
Thank you for your help.
I tried, but nothing worked. Could you please give me an overview, using this example?
Thanks again for your help.
Thank you for your help.
I tried, but nothing worked. Could you please give me an overview, using this example?
Thanks again for your help.
Hello,
I can't convert an Excel table into SAS.
Can I send you a small Excel file to see what the transformation looks like in SAS?
I can't upload it through the forum.
Thank you for your help.
I can't convert an Excel table into SAS.
Can I send you a small Excel file to see what the transformation looks like in SAS?
I can't upload it through the forum.
Thank you for your help.
Check your logs and if you still can't, post the content here.
Try to understand the error, as I cannot do your tasks for you.
to be changed to BOLD
options noxwait noxsync;
x 'D:\excel.exe'; /* THE ACCESS PATH MUST BE MODIFIED to the location of your Excel executable (c:/program files etc...) this is the path that SAS must follow to open Excel */
data _null_;
sleep=mcipislp(3);
run;
filename cmds dde 'excel|system'; /* DO NOT change anything */
data _null_;
file cmds;
put '[open("D:\PT_CODE.xls")]'; /* Indicate the full path and name of the file you want to import if SAS does not find it, a message will appear in the log and the Excel message box stating that the file does not exist */
run;
filename test dde 'excel|Fusion_crash ! r2c1:r38c3' lrecl=2000;
/* "Fusion_crash" must be modified to the name of the sheet if you have not renamed it, it will often be ("Sheet1" or "Feuille1")
r2c1:r38c3 : the range of cells to retrieve r for row, c for column, be sure not to forget the ' just after the 3 in the example
Lrecl if omitted defaults to 256, modify it if the length of the record in your Excel cell is greater */
data z.missing_codes; /* Table name here z, do not forget to allocate the library otherwise import it into work */
attrib
Old_Country_name length=$250 /* Assign the variables and their length, personally I import all variables as numeric with this method to prevent Excel format recognition problems by SAS, then I perform a data step to convert spaces or commas in order to change text variables to numeric */
Country_name length=$250
Country_cd length=$3 ;
infile test dlm='09'x notab dsd truncover pad; /* DO NOT CHANGE ANYTHING */
input
Old_Country_name $
Country_name $
Country_cd $ /* TAKE BACK THE VARIABLE NAMES PREVIOUSLY ASSIGNED */
;
run;
filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
data _null_; /* DO NOT CHANGE ANYTHING To close Excel */
file cmds;
put '[quit()]';
run;
Try to understand the error, as I cannot do your tasks for you.
to be changed to BOLD
options noxwait noxsync;
x 'D:\excel.exe'; /* THE ACCESS PATH MUST BE MODIFIED to the location of your Excel executable (c:/program files etc...) this is the path that SAS must follow to open Excel */
data _null_;
sleep=mcipislp(3);
run;
filename cmds dde 'excel|system'; /* DO NOT change anything */
data _null_;
file cmds;
put '[open("D:\PT_CODE.xls")]'; /* Indicate the full path and name of the file you want to import if SAS does not find it, a message will appear in the log and the Excel message box stating that the file does not exist */
run;
filename test dde 'excel|Fusion_crash ! r2c1:r38c3' lrecl=2000;
/* "Fusion_crash" must be modified to the name of the sheet if you have not renamed it, it will often be ("Sheet1" or "Feuille1")
r2c1:r38c3 : the range of cells to retrieve r for row, c for column, be sure not to forget the ' just after the 3 in the example
Lrecl if omitted defaults to 256, modify it if the length of the record in your Excel cell is greater */
data z.missing_codes; /* Table name here z, do not forget to allocate the library otherwise import it into work */
attrib
Old_Country_name length=$250 /* Assign the variables and their length, personally I import all variables as numeric with this method to prevent Excel format recognition problems by SAS, then I perform a data step to convert spaces or commas in order to change text variables to numeric */
Country_name length=$250
Country_cd length=$3 ;
infile test dlm='09'x notab dsd truncover pad; /* DO NOT CHANGE ANYTHING */
input
Old_Country_name $
Country_name $
Country_cd $ /* TAKE BACK THE VARIABLE NAMES PREVIOUSLY ASSIGNED */
;
run;
filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
data _null_; /* DO NOT CHANGE ANYTHING To close Excel */
file cmds;
put '[quit()]';
run;
Hello Sir,
I apologize for contacting you in this way.
Indeed, I tried to adapt your Excel file import program on SAS, but when executing, I get this message:
Excel file | system does not exist.
Moreover, I would like to know if the location of the Excel executable is the same as the location of Excel.
Could you help me resolve this issue?
I apologize for contacting you in this way.
Indeed, I tried to adapt your Excel file import program on SAS, but when executing, I get this message:
Excel file | system does not exist.
Moreover, I would like to know if the location of the Excel executable is the same as the location of Excel.
Could you help me resolve this issue?
Hello,
I see that there are many people who know SAS and Excel in this discussion...
I actually have a problem with Excel and SAS:
I use Excel files to enter data into SAS, and during the import, I encounter some issues.
Here is the detailed process I use:
1. The Excel files contain data in columns
2. We save the Excel files in csv format
3. The csv files are imported into SAS
During phase 3, we get strange results due to a problem during phase 2:
- If we have 5 columns in Excel, we sometimes find a small square in SAS in the 5th column
- Sometimes, we find this small square in SAS in the 3rd or 4th column
These squares are due to the absence of a ';' at the end of a line in the csv file.
My question is: How can I avoid these squares without adding an unnecessary column in my Excel file?
Thank you in advance, and sorry for polluting this discussion with another topic!
Thanks!
I see that there are many people who know SAS and Excel in this discussion...
I actually have a problem with Excel and SAS:
I use Excel files to enter data into SAS, and during the import, I encounter some issues.
Here is the detailed process I use:
1. The Excel files contain data in columns
2. We save the Excel files in csv format
3. The csv files are imported into SAS
During phase 3, we get strange results due to a problem during phase 2:
- If we have 5 columns in Excel, we sometimes find a small square in SAS in the 5th column
- Sometimes, we find this small square in SAS in the 3rd or 4th column
These squares are due to the absence of a ';' at the end of a line in the csv file.
My question is: How can I avoid these squares without adding an unnecessary column in my Excel file?
Thank you in advance, and sorry for polluting this discussion with another topic!
Thanks!
If you detail the program you use for the little 3, it could be useful.
What do you do? Do you go through the wizard?
If you want to retrieve CSV documents, I recommend going through a data step using the infile.
Are you sure that in certain lines you are missing this separator? In that case, options truncover and @@ allow you with the infile to identify the missing values and prevent reading from moving to the next line (SAS thinking it will find the contents of the 5th column in the first of the next line)
But your issue makes me think of special characters that SAS might not recognize, which could stem from step 2.
Since step 2 can be the source of an error, I would advise importing Excel files directly using the small program above that uses DDE links.
What do you do? Do you go through the wizard?
If you want to retrieve CSV documents, I recommend going through a data step using the infile.
Are you sure that in certain lines you are missing this separator? In that case, options truncover and @@ allow you with the infile to identify the missing values and prevent reading from moving to the next line (SAS thinking it will find the contents of the 5th column in the first of the next line)
But your issue makes me think of special characters that SAS might not recognize, which could stem from step 2.
Since step 2 can be the source of an error, I would advise importing Excel files directly using the small program above that uses DDE links.
Hello,
I just tried the program you posted by adapting it to my file, but I am getting an error (The physical file does not exist, excel|system.) that I cannot resolve, maybe you can help me?
Thank you in advance.
The adapted program:
options noxwait noxsync;
x 'C:\excel.exe'; /* THE PATH MUST BE CHANGED to the location of your excel executable (c:/program files etc...) this is the path that sas must follow to open Excel*/
data _null_;
sleep=mcipislp(3);
run;
filename cmds dde 'excel|system'; /* Do not change anything */
data _null_;
file cmds;
put '[open("C:\test_sas.xls")]'; /* Indicate the full path and name of the file you want to import if SAS cannot find it an error message will appear in the log and the message box of excel stating that the file does not exist */
run;
filename test dde 'excel|feuil1 ! r1c1:r4c4' ;
/* "Fusion_crash" should be modified to the name of the sheet if you haven't renamed it it will often be ("feuille1" or "sheet1")
r2c1:r38c3 : the range of cells to retrieve r for row, c for column, make sure not to forget the ' right after the 3 in the example
Lrecl if omitted by default 256 modify it if the length of the record in your excel cell is greater */
data lorraine; /* Name of the table here z, do not forget to allocate the library otherwise import it into work */
attrib
reg length=$5
dep length=$5
CG length=$5
LIBGEO length=$50 ;
infile test dlm='09'x notab dsd truncover pad; /* DO NOT CHANGE ANYTHING */
input
reg $
dep $
CG $
LIBGEO $
;
run;
filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
data _null_; /* DO NOT CHANGE ANYTHING To close Excel */
file cmds;
put '[quit()]';
run;
I just tried the program you posted by adapting it to my file, but I am getting an error (The physical file does not exist, excel|system.) that I cannot resolve, maybe you can help me?
Thank you in advance.
The adapted program:
options noxwait noxsync;
x 'C:\excel.exe'; /* THE PATH MUST BE CHANGED to the location of your excel executable (c:/program files etc...) this is the path that sas must follow to open Excel*/
data _null_;
sleep=mcipislp(3);
run;
filename cmds dde 'excel|system'; /* Do not change anything */
data _null_;
file cmds;
put '[open("C:\test_sas.xls")]'; /* Indicate the full path and name of the file you want to import if SAS cannot find it an error message will appear in the log and the message box of excel stating that the file does not exist */
run;
filename test dde 'excel|feuil1 ! r1c1:r4c4' ;
/* "Fusion_crash" should be modified to the name of the sheet if you haven't renamed it it will often be ("feuille1" or "sheet1")
r2c1:r38c3 : the range of cells to retrieve r for row, c for column, make sure not to forget the ' right after the 3 in the example
Lrecl if omitted by default 256 modify it if the length of the record in your excel cell is greater */
data lorraine; /* Name of the table here z, do not forget to allocate the library otherwise import it into work */
attrib
reg length=$5
dep length=$5
CG length=$5
LIBGEO length=$50 ;
infile test dlm='09'x notab dsd truncover pad; /* DO NOT CHANGE ANYTHING */
input
reg $
dep $
CG $
LIBGEO $
;
run;
filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
data _null_; /* DO NOT CHANGE ANYTHING To close Excel */
file cmds;
put '[quit()]';
run;
Back to the previous message, I found the localization error, here is now the "fatal" error displayed in the log:
ERROR: DDE session is not ready.
FATAL: Unrecoverable I/O error detected during the execution of the data step. Interrupted during the EXECUTION phase.
ERROR: DDE session is not ready.
FATAL: Unrecoverable I/O error detected during the execution of the data step. Interrupted during the EXECUTION phase.
Hello and thank you for posting your errors.
This error is due to Excel not being ready and the data step failing to retrieve information from the Excel sheet.
Two things may be the source of the error:
- the pause time between opening Excel and the step is too short, and the data step cannot be executed (if the machine is old and Excel starts slowly or if the file is large...)
> try increasing the pause
sleep=mcipislp(15);
- Excel is already open, and it is performing operations that prevent the file from opening
I rather think the issue should be looked into on the Excel side; your code has been well adapted.
Did you create a shortcut for Excel directly on C? If not, try specifying the original installation path of Excel...
I hope I have been helpful.
This error is due to Excel not being ready and the data step failing to retrieve information from the Excel sheet.
Two things may be the source of the error:
- the pause time between opening Excel and the step is too short, and the data step cannot be executed (if the machine is old and Excel starts slowly or if the file is large...)
> try increasing the pause
sleep=mcipislp(15);
- Excel is already open, and it is performing operations that prevent the file from opening
I rather think the issue should be looked into on the Excel side; your code has been well adapted.
Did you create a shortcut for Excel directly on C? If not, try specifying the original installation path of Excel...
I hope I have been helpful.
Hello,
I have indeed adjusted the time, I set it to 60 seconds (below). If Excel is not already open, it cannot find the source file ("ERROR: The physical file does not exist, excel|system."). When Excel is open, and despite an extended time, it still shows me the same error message:
ERROR: DDE session is not ready.
FATAL: Non-recoverable I/O error detected during program execution
data step. Interrupted during the EXECUTION phase.
NOTE: The SAS System has stopped processing this step due to
errors.
(the log is detailed below as well).
Could it be a module defect? I only have the SAS PERSONAL ANALYTICS module...
Thank you in advance.
/******************** PROGRAM ****************/
libname x 'C:\table.sas7dbat';
options pagesize = 60 linesize = 80;
options nodate;
run;
options noxwait noxsync;
x 'C:\Program Files\Microsoft Office\Office10\excel.exe'; /* THE ACCESS PATH SHOULD BE MODIFIED to the location of your Excel executable (c:/program files etc...) this is the path that SAS must follow to open Excel*/
data _null_;
sleep=mcipislp(60);
run;
filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
data _null_;
file cmds;
put '[open("C:\test_sas.xls")]'; /*Indicate the complete path and the name of the file you wish to import if SAS does not find it a message will appear in the log and Excel's message box saying that the file does not exist */
run;
filename test dde 'excel|Sheet1!r1c1:r4c4' ;
/* "Fusion_crash" should be replaced by the name of the sheet if you have not renamed it will often be ("Sheet1" or "sheet1")
r2c1:r38c3: the range of cells to retrieve r for row, c for column, be careful not to forget the ' just after the 3 in the example
Lrecl if omitted defaults to 256 modify it if the length of the record in your Excel cell is greater */
data x.lorraine; /* Name of the table here z, do not forget to allocate the library otherwise import it into work */
attrib
reg length=$5
dep length=$5
CG length=$5
LIBGEO length=$50 ;
infile test dlm='09'x notab dsd truncover pad; /* DO NOT CHANGE ANYTHING */
input
reg $
dep $
CG $
LIBGEO $;
run;
filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
data _null_; /*DO NOT CHANGE TO close Excel */
file cmds;
put '[quit()]';
run;
/*******************LOG*************/
61 options noxwait noxsync;
62 x 'C:\Program Files\Microsoft Office\Office10\excel.exe'
62 ! ; /* THE ACCESS PATH
62 ! SHOULD BE MODIFIED to the location of your Excel executable
62 ! (c:/program files etc...) this is the path that SAS must follow to open
62 ! Excel*/
63
64 data _null_;
65 sleep=mcipislp(60);
66 run;
NOTE: The DATA step utilized (Total duration of the process):
real time 1:00.02
CPU time 0.32 seconds
67 filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
68
69 data _null_;
70 file cmds;
71 put '[open("C:\test_sas.xls")]'; /*Indicate the complete path and the name of the
71 ! file you wish to import if SAS does not find it a message
71 ! will appear in the log and Excel's message box saying that the file
71 ! does not exist */
72 run;
NOTE: The CMDS file is:
DDE Session,
SESSION=excel|system,RECFM=V,LRECL=256
NOTE: 1 record copied to the CMDS file.
The minimum length of the record was 25.
The maximum length of the record was 25.
NOTE: The DATA step used (Total duration of the process):
real time 0.29 seconds
CPU time 0.01 seconds
73
74 filename test dde 'excel|Sheet1!r1c1:r4c4' ;
75 data x.lorraine; /* Name of the table here z, do not forget to allocate the
75 ! library otherwise import it into work */
76 attrib
77 reg length=$5
78 dep length=$5
79 CG length=$5
80 LIBGEO length=$50 ;
81 infile test dlm='09'x notab dsd truncover pad; /* DO NOT CHANGE ANYTHING */
82
83 input
84 reg $
85 dep $
86 CG $
87 LIBGEO $;
88 run;
NOTE: The infile TEST is:
DDE Session,
SESSION=excel|Sheet1!r1c1:r4c4,RECFM=V,
LRECL=256
ERROR: DDE session is not ready.
FATAL: Non-recoverable I/O error detected during program execution
data step. Interrupted during the EXECUTION phase.
NOTE: The SAS System has stopped processing this step due to
errors.
WARNING: The table X.LORRAINE may be incomplete. When this
step was stopped, there were 0 observations and 4 variables.
WARNING: Table X.LORRAINE not replaced as this step was interrupted.
NOTE: The DATA step used (Total duration of the process):
real time 0.07 seconds
CPU time 0.05 seconds
I have indeed adjusted the time, I set it to 60 seconds (below). If Excel is not already open, it cannot find the source file ("ERROR: The physical file does not exist, excel|system."). When Excel is open, and despite an extended time, it still shows me the same error message:
ERROR: DDE session is not ready.
FATAL: Non-recoverable I/O error detected during program execution
data step. Interrupted during the EXECUTION phase.
NOTE: The SAS System has stopped processing this step due to
errors.
(the log is detailed below as well).
Could it be a module defect? I only have the SAS PERSONAL ANALYTICS module...
Thank you in advance.
/******************** PROGRAM ****************/
libname x 'C:\table.sas7dbat';
options pagesize = 60 linesize = 80;
options nodate;
run;
options noxwait noxsync;
x 'C:\Program Files\Microsoft Office\Office10\excel.exe'; /* THE ACCESS PATH SHOULD BE MODIFIED to the location of your Excel executable (c:/program files etc...) this is the path that SAS must follow to open Excel*/
data _null_;
sleep=mcipislp(60);
run;
filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
data _null_;
file cmds;
put '[open("C:\test_sas.xls")]'; /*Indicate the complete path and the name of the file you wish to import if SAS does not find it a message will appear in the log and Excel's message box saying that the file does not exist */
run;
filename test dde 'excel|Sheet1!r1c1:r4c4' ;
/* "Fusion_crash" should be replaced by the name of the sheet if you have not renamed it will often be ("Sheet1" or "sheet1")
r2c1:r38c3: the range of cells to retrieve r for row, c for column, be careful not to forget the ' just after the 3 in the example
Lrecl if omitted defaults to 256 modify it if the length of the record in your Excel cell is greater */
data x.lorraine; /* Name of the table here z, do not forget to allocate the library otherwise import it into work */
attrib
reg length=$5
dep length=$5
CG length=$5
LIBGEO length=$50 ;
infile test dlm='09'x notab dsd truncover pad; /* DO NOT CHANGE ANYTHING */
input
reg $
dep $
CG $
LIBGEO $;
run;
filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
data _null_; /*DO NOT CHANGE TO close Excel */
file cmds;
put '[quit()]';
run;
/*******************LOG*************/
61 options noxwait noxsync;
62 x 'C:\Program Files\Microsoft Office\Office10\excel.exe'
62 ! ; /* THE ACCESS PATH
62 ! SHOULD BE MODIFIED to the location of your Excel executable
62 ! (c:/program files etc...) this is the path that SAS must follow to open
62 ! Excel*/
63
64 data _null_;
65 sleep=mcipislp(60);
66 run;
NOTE: The DATA step utilized (Total duration of the process):
real time 1:00.02
CPU time 0.32 seconds
67 filename cmds dde 'excel|system'; /* DO NOT CHANGE ANYTHING */
68
69 data _null_;
70 file cmds;
71 put '[open("C:\test_sas.xls")]'; /*Indicate the complete path and the name of the
71 ! file you wish to import if SAS does not find it a message
71 ! will appear in the log and Excel's message box saying that the file
71 ! does not exist */
72 run;
NOTE: The CMDS file is:
DDE Session,
SESSION=excel|system,RECFM=V,LRECL=256
NOTE: 1 record copied to the CMDS file.
The minimum length of the record was 25.
The maximum length of the record was 25.
NOTE: The DATA step used (Total duration of the process):
real time 0.29 seconds
CPU time 0.01 seconds
73
74 filename test dde 'excel|Sheet1!r1c1:r4c4' ;
75 data x.lorraine; /* Name of the table here z, do not forget to allocate the
75 ! library otherwise import it into work */
76 attrib
77 reg length=$5
78 dep length=$5
79 CG length=$5
80 LIBGEO length=$50 ;
81 infile test dlm='09'x notab dsd truncover pad; /* DO NOT CHANGE ANYTHING */
82
83 input
84 reg $
85 dep $
86 CG $
87 LIBGEO $;
88 run;
NOTE: The infile TEST is:
DDE Session,
SESSION=excel|Sheet1!r1c1:r4c4,RECFM=V,
LRECL=256
ERROR: DDE session is not ready.
FATAL: Non-recoverable I/O error detected during program execution
data step. Interrupted during the EXECUTION phase.
NOTE: The SAS System has stopped processing this step due to
errors.
WARNING: The table X.LORRAINE may be incomplete. When this
step was stopped, there were 0 observations and 4 variables.
WARNING: Table X.LORRAINE not replaced as this step was interrupted.
NOTE: The DATA step used (Total duration of the process):
real time 0.07 seconds
CPU time 0.05 seconds
Indeed, this error seems unusual.
The DDE links are the oldest data exchange system used by SAS.
It does not require any additional modules.
It seems that SAS is simply unable to communicate with your OS.
Some information about your workstation could help to resolve this issue.
The DDE links are the oldest data exchange system used by SAS.
It does not require any additional modules.
It seems that SAS is simply unable to communicate with your OS.
Some information about your workstation could help to resolve this issue.
Hello,
I'm trying to import a txt or csv file into SAS.
Some columns are being converted to text even though they are numeric before the transfer, how can I resolve this issue? THANK YOU
I'm trying to import a txt or csv file into SAS.
Some columns are being converted to text even though they are numeric before the transfer, how can I resolve this issue? THANK YOU
You should check in your program that you specify that the first line of the Excel file contains the column headers.
It is also possible that a letter appears in a modality of this column. Could you take a quick look at all the modalities?
Another solution, after having carefully checked that you have not had a shift in your modalities in your table imported into SAS (for example, if one of the variables contains commas, SAS will interpret it as a separator and consider what is after the comma as a separate modality), you can recode your text variable to a numerical format.
It is also possible that a letter appears in a modality of this column. Could you take a quick look at all the modalities?
Another solution, after having carefully checked that you have not had a shift in your modalities in your table imported into SAS (for example, if one of the variables contains commas, SAS will interpret it as a separator and consider what is after the comma as a separate modality), you can recode your text variable to a numerical format.
You might need to use periods instead of your commas. The comma is generally used as a field separator and not for the decimal. With the Anglo-Saxon system, the period replaces the comma. I hope your problem comes from there.
Hello,
I have been a SAS user for quite some time now. I know how to program in SAS and import Excel files without any problems.
However, at work, I am required to use Enterprise Guide to import Excel files, so I do not program by hand.
I am having trouble importing Excel files. Let me explain:
I have an Excel file of the following type:
T Price
Q41988
Q11989
Q21989 45365
Q31989
Q41989 1346.00
When I import to Enterprise Guide, SAS does not recognize the missing values, and as a result, all the lines are filled with dots. This is a formatting or informat issue, but I have not yet found the solution.
If you have any ideas,
Thank you in advance.
I have been a SAS user for quite some time now. I know how to program in SAS and import Excel files without any problems.
However, at work, I am required to use Enterprise Guide to import Excel files, so I do not program by hand.
I am having trouble importing Excel files. Let me explain:
I have an Excel file of the following type:
T Price
Q41988
Q11989
Q21989 45365
Q31989
Q41989 1346.00
When I import to Enterprise Guide, SAS does not recognize the missing values, and as a result, all the lines are filled with dots. This is a formatting or informat issue, but I have not yet found the solution.
If you have any ideas,
Thank you in advance.
Hello,
I am not familiar with enterprise guide; however, is your issue not related to the comma value that I see in your table (1346,00)? In SAS, certain characters cause problems during import, and the comma is one of them. You need to replace it with a point. Unless it's different in enterprise guide...
Good manipulation...
I am not familiar with enterprise guide; however, is your issue not related to the comma value that I see in your table (1346,00)? In SAS, certain characters cause problems during import, and the comma is one of them. You need to replace it with a point. Unless it's different in enterprise guide...
Good manipulation...
- 1
- 2
Next
SAS doesn't like commas!!
:op
I went on the forum and I feel like you are proficient in SAS software. I asked my boss for training on SAS and he told me to show him how it could benefit our logistics and transport company by using this software. I have an Excel spreadsheet and I would like to import it into SAS to draw some statistical conclusions.
Could you please help me?
Thank you in advance.
P.S.: I can send you my small Excel spreadsheet with my shipments if you want.
Thank you
jamesfrancourt@yahoo.fr
I have a .csv file that I'm trying to import into SAS. The delimiters are commas, but additionally, the strings are enclosed in " which is causing me problems. Moreover, I have a few records where the string contains a carriage return.
I found a solution but only for SAS 9.2 (with the DLMSTR option) and I am working with SAS 9.1 :(
Otherwise, if I import the said table into Access and then import that same table into SAS, it works. The problem is I have about 300 files to import this way! :(
Thank you for your help.
Charlie