Hi, I found the following MACRO http://www.google.com/url?sa=t&rct=j&q=macro%20exdde%20sas&source=web&cd=2&cad=rja&ved=0CDUQFjAB&url...
to use in SAS; however, I get an error message when I use it. What could be the problem? Also, if there is another way to import data from Excel (preferably xlsx into SAS and be able to systematically format variables), please let me know.
2247 %EXDDE(/*** 1. Parameters related to the input Excel file ***/
-
180
WARNING: Apparent invocation of macro EXDDE not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.
2248 Excel = ON, /* = ON, the Excel file is open; otherwise, the macro opens the Excel file */
2249 Xlsexe =C:\Program Files (x86)\Microsoft Office\Office14, /* Location of Microsoft Excel
2249! on the C: drive */
2250 Rawdir =U:\HC Diabetes\datacuts, /* REQUIRED. Input Excel file directory */
2251 File = DM_Pilot_revisions_09242012, /* REQUIRED. Input Excel file name */
2252 Sheet = Sheet4,/* Worksheet name(s), e.g., Sheet1|Sheet2. Sheet1 by default. */
2253 Lrecl = 60, /* Length of the Excel file, default is 256 */
2254 StartR =2, /* Row number of the 1st cell to read */
2255 StartC =1, /* Column number of the 1st cell to read */
2256 EndR =11, /* Row number of the last cell to read */
2257 EndC =60, /* Column number of the last cell to read */
2258 /*** 2. Parameters related to the output SAS dataset ***/
2259 Var =, form IDcode_new TTMID race_ethnicity race_other startdate enddate
2259! Diabetes_type_1 Diabetes_type_2 Diabetes_type_no Diabetes_type_unkn Diabetes_screen
2259! Wt_baseline Hba1c_baseline DOB_claims DOB_chart Gender_chart Gender_claims
2259! education_completed employment T2Dm_date Chart_type height_value Height_units
2259! Smoker years_smoked units_smoker BMI_values BMI_1 BMI_2 BMI_3 BMI_4
2259! HbA1c_1 Weight_1 weight_units_1 weight_date_1 Weight_2 weight_units_2 Weight_3
2259! weight_units_3 Weight_4 Weight_5 Weight_6 weight_units_6 weight_units_5
2259! weight_units_4 weight_date_2 weight_date_3 weight_date_4 weight_date_5
2259! weight_date_6 Weight_7 weight_units_7 weight_date_7 Weight_8 weight_units_8
2259! weight_date_8 weight_units_9 Weight_9 weight_date_9
2260 Fmt = best32. 10. 14. 2. $32. mmddyy10. mmddyy10. 2. 2. 2. 2. 2. 10. 10.
2260! mmddyy10. $10. 2. $10. 2. 2. $10. 2. 10. 2. 2. 10. 10. 2. 10. 10. 10.
2260! 10. 10. 10. 2. $10. 10. 2. 10. 2. 10. 10. 10. 2. 2. 2. $10. $10. $10.
2260! $10. $10. 10. 2. $10. 10. 2. $10. 2. 10. $10.,
2261 Dum =, /* Extraneous variables need to be dropped */
2262 Flag =001, /* Flag for the sub-sheet origin of the data */
2263 Outdata= dm, /* REQUIRED. Output SAS data set name */
2264
2265 /*** 3. Process related parameters ***/
2266 Sleep =, /* Seconds that SAS is suspended from execution while opening up the Excel file
2266! */
2267 Debug = /* Whether to keep intermediate data sets, default=NO */);
Are you trying to deal with a lot of variables that don't get imported properly?
What version of SAS are you on and do you have SAS/ACCESS?
I am using SAS 9.2.
I do not have SAS/ACCESS.
I presume you are running on a PC, so you should have EGuide. It has some nice tools for importing SAS that don't use SAS/Access.
The initial problem you described is that SAS could not find the macro to run it. You either need to include the macro in a SASAUTOS directory or explicitly include it in the session code with %INCLUDE.
Doc Muhlbaier
Duke
Thanks for your reply. Where I put the %include? Is there a more efficient way to import a lot of variables from excel that don't import properly (look like dates but need to be imported as text, etc)? I still get the following when I add the %include at the beginning of the macro:
14121 %INCLUDE
14122 %EXDDE(/*** 1. Parameters related to the input Excel file ***/
WARNING: Apparent invocation of macro EXDDE not resolved.
14123 Excel = ON, /* = ON, the Excel file is open; otherwise, the macro opens the Excel file */
14124 Xlsexe =C:\Program Files (x86)\Microsoft Office\Office14, /* Location of Microsoft Excel
14124! on the C: drive */
14125 Rawdir =U:\HC Diabetes\datacuts, /* REQUIRED. Input Excel file directory */
14126 File = DM_Pilot_revisions_09242012, /* REQUIRED. Input Excel file name */
14127 Sheet = Sheet4,/* Worksheet name(s), e.g., Sheet1|Sheet2. Sheet1 by default. */
14128 Lrecl = 60, /* Length of the Excel file, default is 256 */
14129 StartR =2, /* Row number of the 1st cell to read */
14130 StartC =1, /* Column number of the 1st cell to read */
14131 EndR =11, /* Row number of the last cell to read */
14132 EndC =60, /* Column number of the last cell to read */
14133 /*** 2. Parameters related to the output SAS dataset ***/
14134 Var =, form IDcode_new TTMID race_ethnicity race_other startdate enddate
14134! Diabetes_type_1 Diabetes_type_2 Diabetes_type_no Diabetes_type_unkn Diabetes_screen
14134! Wt_baseline Hba1c_baseline DOB_claims DOB_chart Gender_chart Gender_claims
14134! education_completed employment T2Dm_date Chart_type height_value Height_units
14134! Smoker years_smoked units_smoker BMI_values BMI_1 BMI_2 BMI_3 BMI_4
14134! HbA1c_1 Weight_1 weight_units_1 weight_date_1 Weight_2 weight_units_2 Weight_3
14134! weight_units_3 Weight_4 Weight_5 Weight_6 weight_units_6 weight_units_5
14134! weight_units_4 weight_date_2 weight_date_3 weight_date_4 weight_date_5
14134! weight_date_6 Weight_7 weight_units_7 weight_date_7 Weight_8 weight_units_8
14134! weight_date_8 weight_units_9 Weight_9 weight_date_9
14135 Fmt = best32. 10. 14. 2. $32. mmddyy10. mmddyy10. 2. 2. 2. 2. 2. 10. 10.
14135! mmddyy10. $10. 2. $10. 2. 2. $10. 2. 10. 2. 2. 10. 10. 2. 10. 10. 10.
14135! 10. 10. 10. 2. $10. 10. 2. 10. 2. 10. 10. 10. 2. 2. 2. $10. $10. $10.
14135! $10. $10. 10. 2. $10. 10. 2. $10. 2. 10. $10.,
14136 Dum =, /* Extraneous variables need to be dropped */
14137 Flag =001, /* Flag for the sub-sheet origin of the data */
14138 Outdata= dm, /* REQUIRED. Output SAS data set name */
14139
14140 /*** 3. Process related parameters ***/
14141 Sleep =, /* Seconds that SAS is suspended from execution while opening up the Excel file
14141! */
14142 Debug = /* Whether to keep intermediate data sets, default=NO */);
ERROR: Incorrect %INCLUDE statement will not be executed. There is a syntax error.
RTFM. %INCLUDE is a statement that takes a filename as it's parameter. The filename is the .sas code that is the macro. You could do it manually, as well, buy just pasting the macro code into your session; then you would not need the %INCLUDE.
How about saving the Excel spreadsheet as a CSV and avoiding the complexity of DDE?
I have done that, but some of the variables read in incorretly. For example, I have dates that have "99" for missing day or month, and these values don't come in. I need the variables to read in as text. I know I can use informat statements to correct the way they are read in, but it's very time consuming. I have one dataset with 1600 variables that need to be read in correctly. If you have any suggestions, I'm very much appreciative.
You can use PROC IMPORT (SAS/ACCESS not required) on a CSV file to create a SAS program that can be tweaked to cater for things like 99 for missing day or month. The code can be seen in the SAS log after running the PROC IMPORT. All you have to do is copy from the log back to your editor window and you have a ready-made DATA step program that can modified to deal with any data issues. A trick with selecting code in your log is to hold down the Alt key when selecting the code block with your mouse. Then just do a Windows Copy and Paste. That way you avoid copying the line numbers.
Do you have the code for the macro %EXDDE?
Its an order of operations things.
You need to run the code that creates the macro and then run the %exdde call, otherwise SAS doesn't know what to do.
The line that follows is another way of running code in SAS, it basically says, 'run the sas code in this file'.
%include 'path to the macro code';
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.