Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 8

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

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)LabelTypeCodesComments
CMPDNUMCompound numberCHAR
PROTOCOLProtocol numberCHAR
ANRandomized subject identifierCHAR
SEQUENCERandomized Dosage SequenceCHAR
PERIODStudy periodNUM
CLASSSubject classificationNUM
AGEAge (yr)NUM
GENDERGenderCHARM = Male
F = Female
RACERaceCHAR
WEIGHTWeight (kg)NUM
HEIGHTHeight (cm)NUM
TREAT_CTreatment codeCHAR
TREATMNTTreatment descriptionCHAR
DOSEDose (mg)NUM DOSE = Nominal dose of analyte.
INFUSTMInfusion time (hr)NUM
ASSAYAssay identifierCHARLC/MS/MS = Mass Spectrometry
MATRIXSample matrixCHARPLASMA
ANALYTEAnalyte identifierCHAR
Efavirenz
DAYStudy DayNUM
NOMTMNominal time (hr)NUM
STARTTMStart time (hr)NUM
ENDTMEnd time (hr)NUM
RESTYPEResult typeCHARCONC = Concentration
RESVALUEResult valueNUM
RESUNITResult unitCHAR
Super User
Posts: 10,497

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

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?

Occasional Contributor
Posts: 8

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

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.

Super User
Posts: 10,497

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

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?

Occasional Contributor
Posts: 8

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

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.

Super User
Posts: 10,497

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

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.

Ask a Question
Discussion stats
  • 5 replies
  • 307 views
  • 0 likes
  • 2 in conversation