I have not used SAS in several years but am back needing to use it frequently. I am having trouble getting SAS to properly recognize and create numeric columns imported (PROC Import) from xlsx and csv files. Looking at old code, I did not do anything to help SAS recognize what columns were numeric vs. character. Even when SAS is doing a calculation and creating what should be a new numeric column in a dataset, it is assigning it as character. I know there are work arounds to get SAS to recognize and handle the information correctly, but I am curious if something has changed over the last several years that has made SAS less intuitive in the import process. I have a person helping me that is much more knowledgeable than I am with SAS and even he has been surprised at the difficulty I have had with getting SAS to recognize and create numeric data. The issue seems to be in the Proc Import statement and relates to not being supported with the V9 engine. My excel file is in xlsx format. I have a lot of missing data represented as . in my excel file. Any help would be appreciated. Here is the first part of my code. dm "output" clear; dm "log" clear; Proc Import datafile = "J:\WRRI\Everyone\Transport MetaAnalysis\Pesticide transport meta-analysis\Pesticide transport meta-analysis.xlsx" out=mydata DBMS=xlsx replace; Sheet='Master'; run; data a; set mydata; keep Latitude Longitude Sand Silt Clay OM OC pH Soil_type Crop_rotation Crop Rainfall_type Cover_crop Pest_incorporated KOW logP Water_solubility Year_in_trial Type_resp_var Response_variable Measure Year_weights Analysis CT_NT_resp_ratio CT_ConsT_resp_ratio ConsT_NT_resp_ratio; if cover_crop = "Yes" or Pest_incorporated = "Yes" or Analysis = "SEdiff" or Year_in_trial = "." or response_variable ne "Water pesticide loss" then delete; run; proc sort data=a; by response_variable year_in_trial KOW OC;
... View more