topic Re: Proc Import/different formats on the date and time varaible in SAS Programming

Proc Import/different formats on the date and time varaible

Manvel — Thu, 23 Nov 2023 14:26:10 GMT

Hello!

I am using Proc Import for importing Excel csv file, I have situation when the excel file collected in different way and SAS log is showing issues when I am just using this code(below). I tried to use "Infile" but again the same issues are showed.

Example from excel file:

Proc Import File = "filelocation"
    Out = test
    Dbms = csv
    replace;
Run;

*Infile code;
  Infile "filelocation"
  delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;										
  Informat STUDYID $15. SITENUM BEST32. SUBNUM BEST32. VISIT $35. XXDT date11. XXTIM TIME20.3;
  Format   STUDYID $15. SITENUM BEST12. SUBNUM BEST12. VISIT $35. XXDT date11. XXTIM TIME20.3;
  Input STUDYID $ SITENUM $ SUBNUM $ VISIT $ XXDT XXTIM;										
run;

Re: Proc Import/different formats on the date and time varaible

PaigeMiller — Thu, 23 Nov 2023 14:30:36 GMT

... and SAS log is showing issues ...

What issues? Show us the LOG for this PROC IMPORT (all of it, every single line in the log for this PROC IMPORT).

Re: Proc Import/different formats on the date and time varaible

Manvel — Thu, 23 Nov 2023 14:41:56 GMT

I masked not necessary variable names and paths.

Re: Proc Import/different formats on the date and time varaible

LinusH — Thu, 23 Nov 2023 15:25:49 GMT

So this log is for a data step import?

Clearly your code does bot align with your data, and without an infile, it's hard to tell what your are doing wrong.

Also, your example of input data does not match your input code.

Can you ellaborate around "...when the excel file collected in different way"?

Re: Proc Import/different formats on the date and time varaible

Tom — Thu, 23 Nov 2023 16:24:27 GMT

Do you have an EXCEL file? Like in your photograph? If it is an EXCEL file (like an XLSX file) then you will have to use PROC IMPORT to read it.

Or do you have a TEXT FILE? Like in your code? In that case DO NOT let EXCEL open it automatically. And if you do accidental do open it that way DO NOT SAVE it. EXCEL will change the values. If you have already done this then try to go back to the original file you received before you opened it with EXCEL.

If you have a TEXT file then share a few sample lines of the file and we can advise on what INFORMAT to use the read the values.

Hint: Don't use BEST as an INFORMAT. It is the name of a FORMAT and using it as the name of an INFORMAT will just be confusing since there is no "best" way to store a number. SAS stores all numbers as 64 bit binary floating point values.