Transferring data from Excel to SAS

[kstringer]

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 */);

[Reeza]

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?

[kstringer]

I am using SAS 9.2.
I do not have SAS/ACCESS.

[Doc_Duke]

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

[kstringer]

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.

[Doc_Duke]

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.

[SASKiwi]

How about saving the Excel spreadsheet as a CSV and avoiding the complexity of DDE?

[kstringer]

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.

[SASKiwi]

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.

[Reeza]

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';