topic Re: Errors reading data from Excel file into SAS (informat & format function) in New SAS User

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

Kalu93 — Thu, 22 Nov 2018 05:33:08 GMT

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.

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

Kurt_Bremser — Thu, 22 Nov 2018 05:50:15 GMT

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

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

singhsahab — Thu, 22 Nov 2018 06:56:45 GMT

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,

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

Reeza — Thu, 22 Nov 2018 15:54:01 GMT

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.

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

Kalu93 — Fri, 23 Nov 2018 01:13:18 GMT

Thank you for the clarification 🙂

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

Kalu93 — Fri, 23 Nov 2018 01:15:03 GMT

Thank you 🙂

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

Kalu93 — Fri, 23 Nov 2018 01:15:33 GMT

Thank you for the clarification 🙂