need help importing excel, SAS actually omitting records in import proc weird.. never seen this before

[Virat]

Hi ALL,

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

   replace;

  range = "RAW$A3:E60";

   getnames = yes;

   guessingrows = 2147483647;

run;

EXCEL FILE

Table
Raw Data
Variable (A3)	Label	Type	Codes	Comments
CMPDNUM	Compound number	CHAR
PROTOCOL	Protocol number	CHAR
AN	Randomized subject identifier	CHAR
SEQUENCE	Randomized Dosage Sequence	CHAR
PERIOD	Study period	NUM
CLASS	Subject classification	NUM
AGE	Age (yr)	NUM
GENDER	Gender	CHAR	M = Male
			F = Female
RACE	Race	CHAR
WEIGHT	Weight (kg)	NUM
HEIGHT	Height (cm)	NUM
TREAT_C	Treatment code	CHAR
TREATMNT	Treatment description	CHAR
DOSE	Dose (mg)	NUM		DOSE = Nominal dose of analyte.
INFUSTM	Infusion time (hr)	NUM
ASSAY	Assay identifier	CHAR	LC/MS/MS = Mass Spectrometry
MATRIX	Sample matrix	CHAR	PLASMA
ANALYTE	Analyte identifier	CHAR
			Efavirenz
DAY	Study Day	NUM
NOMTM	Nominal time (hr)	NUM
STARTTM	Start time (hr)	NUM
ENDTM	End time (hr)	NUM
RESTYPE	Result type	CHAR	CONC = Concentration
RESVALUE	Result value	NUM
RESUNIT	Result unit	CHAR

[ballardw]

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?

[Virat]

Thanks Ballardw.

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.

[ballardw]

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?

[Virat]

Thans Ballardw,

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.

[ballardw]

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.