cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
kstringer
‎09-01-2015
  • 4 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Transferring data from Excel to SAS

by Calcite | Level 5 in SAS Programming
‎02-06-2013 04:20 PM
‎02-06-2013 04:20 PM
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. ... View more

Re: Transferring data from Excel to SAS

by Calcite | Level 5 in SAS Programming
‎02-05-2013 09:09 AM
‎02-05-2013 09:09 AM
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. ... View more

Re: Transferring data from Excel to SAS

by Calcite | Level 5 in SAS Programming
‎02-04-2013 03:59 PM
‎02-04-2013 03:59 PM
I am using SAS 9.2. I do not have SAS/ACCESS. ... View more

Transferring data from Excel to SAS

by Calcite | Level 5 in SAS Programming
‎02-04-2013 03:37 PM
‎02-04-2013 03:37 PM
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 */); ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎09-01-2015 07:12 AM
Quick links