topic Re: Transferring data from Excel to SAS in SAS Programming

Transferring data from Excel to SAS

kstringer — Mon, 04 Feb 2013 20:37:06 GMT

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=http%3A%2F%2Fwww2.sas.com%2Fproceedings%2Fsugi29%2F082-29.pdf&ei=xRcQUZmNAYig8QTaxoCYAw&usg=AFQjCNFmUvtPwRiiumePY9QR6f9cs4uPFQ&bvm=bv.41867550,d.eWU

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

Re: Transferring data from Excel to SAS

Reeza — Mon, 04 Feb 2013 20:47:01 GMT

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?

Re: Transferring data from Excel to SAS

kstringer — Mon, 04 Feb 2013 20:59:31 GMT

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

Re: Transferring data from Excel to SAS

Doc_Duke — Tue, 05 Feb 2013 13:58:35 GMT

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

Re: Transferring data from Excel to SAS

kstringer — Tue, 05 Feb 2013 14:09:13 GMT

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.

Re: Transferring data from Excel to SAS

Doc_Duke — Tue, 05 Feb 2013 14:35:50 GMT

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.

Re: Transferring data from Excel to SAS

SASKiwi — Wed, 06 Feb 2013 18:55:26 GMT

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

Re: Transferring data from Excel to SAS

kstringer — Wed, 06 Feb 2013 21:20:54 GMT

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.

Re: Transferring data from Excel to SAS

Reeza — Wed, 06 Feb 2013 21:38:17 GMT

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

Re: Transferring data from Excel to SAS

SASKiwi — Wed, 06 Feb 2013 22:04:09 GMT

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.