06-26-2015 04:06 PM
I am trying to import below data using the proc import. i noticed, two things weird in the imported excel file. SAS missing letter "D" COMPDNUM in the first record of variable "Variable". Also SAS not reading "RACE" and INFUSTM.
so i am missing RACE and INFUSTM in imported SAS dataset and missing "D" in records COMPDNUM. I check this data are present in the define.xls file.
does anyone know why SAS doing this? Am i missing something in import procedure.
IMPORT PROC used
filename define 'C:\Users\USER\Documents\define.xls';
proc import datafile = define
out = Define_RAW1
DBMS = xls
range = "RAW$A3:E60";
getnames = yes;
guessingrows = 2147483647;
|AN||Randomized subject identifier||CHAR|
|SEQUENCE||Randomized Dosage Sequence||CHAR|
|GENDER||Gender||CHAR||M = Male|
|F = Female|
|DOSE||Dose (mg)||NUM||DOSE = Nominal dose of analyte.|
|INFUSTM||Infusion time (hr)||NUM|
|ASSAY||Assay identifier||CHAR||LC/MS/MS = Mass Spectrometry|
|NOMTM||Nominal time (hr)||NUM|
|STARTTM||Start time (hr)||NUM|
|ENDTM||End time (hr)||NUM|
|RESTYPE||Result type||CHAR||CONC = Concentration|
06-26-2015 04:14 PM
Be cautious with long titles on subjects in this forum. They can make it impossible to reply to for some reason.
Guessing rows doesn't work with Excel, there is a Jet engine bit in the registry that exposes information to SAS. If the first 20 or so records of CMPDNUM (or COMPDNUM) contained only digits then SAS likely treated the column as numeric so D is not a valid number. You may need the MIXED option.
It would be helpful to run proc contents on the resulting SAS data set to see the variable types assigned.
You may need to describe more about what you mean "SAS not reading "RACE" and INFUSTM". Are all value missing, some values missing, the variables aren't the data set at all, or are there values you don't expect?
06-26-2015 09:11 PM
I will take care next time for about the title.
For "RACE" and INFUSTM are the records in the variable named "Variable". So in source excel file, there are 25 records 5 variables (Variable, Label, Type, codes, comments) . After running the import procedure above, the output SAS dataset has only 23 records and 5 variable. I manually checked and found records for RACE and INFUSTM is missing in the imported SAS dataset.
06-29-2015 12:01 PM
I would seriously consider exporting the Excel to CSV. Some of the odd things that people put into Excel are sometimes revealed.
I note that RACE occurs after and entry with the CODE field displayed in two lines. Sometimes the characters used to do that cause issues.
It also appears that you re reading from 5 columns (E60) but only show 4 in your example. Or did the forum eat the first column when pasting the example data?
06-29-2015 12:50 PM
I will try exporting the excel to CSV.
Probably you might be right the Code field might cause an issue. I did not notice it.
The fifth column is comment column. Apparent most of observation are empty for this variable.
06-29-2015 01:13 PM
One thing with CSV when using Proc import, it generates the actual data step code used to read the file. Look in the log. If a variable comes in with an unexpected characteristic you can copy the code from the log, change the informat, format and such and rerun the code. Also you may get better warning messages about odd bits in your data.
If the log shows something odd don't be afraid to post it for help.