Errors reading data from Excel file into SAS (informat & format function)

[Kalu93]

Hi all,

I'm very new to SAS and trying to import data from this excel file using the infile data step (i managed to import the data successfully using the import proc step) and can't seem to understand why its not working.

I am trying to display the dates and associated variable into SAS however every time i run this code, SAS generates a . for each observation.

I thought it was a formatting error and have tried multiple ways to read this information i can't figure it out, any help is appreciated 🙂

The code and outputs i entered are below, specifically the use of informat and format functions;

Also, only the first 33 of 123 observations are read,

Thank you.

[Kurt_Bremser]

Infile and input in the data step are for text files. Excel files need proc import or libname Excel.

[Kalu93]

Thank you for the clarification 🙂

[singhsahab · Accepted solution]

Hi Kalu,

Welcome !! You can use PROC Import procedure .like below. 

proc import datafile="\\path\data.xls"
out=want dbms=excel replace;
run;

proc print data=want;
format date ddmmyy8.;
run;

Thanks,

[Kalu93]

Thank you 🙂

[Reeza]

To import an XLSX or XLS file you need to use PROC IMPORT. And unfortunately there isn't a way to specify what format/informat you want for the variables as you read them in. If you want to get around that you can convert the file to a csv and then write a data step to import the file. 

---

@Kalu93 wrote:

Hi all,

I'm very new to SAS and trying to import data from this excel file using the infile data step (i managed to import the data successfully using the import proc step) and can't seem to understand why its not working.

I am trying to display the dates and associated variable into SAS however every time i run this code, SAS generates a . for each observation.

I thought it was a formatting error and have tried multiple ways to read this information i can't figure it out, any help is appreciated 🙂

The code and outputs i entered are below, specifically the use of informat and format functions;

Also, only the first 33 of 123 observations are read,

Thank you.

---

[Kalu93]

Thank you for the clarification 🙂