topic Re: Using informats for time data in SAS Programming

Using informats for time data

_maldini_ — Wed, 09 Mar 2016 17:09:41 GMT

Can I assign informats to specific varialbes using PROC IMPORT and an Excel file, or in a DATA step (The resources I have seen for assigning informats all seem to reference using an INPUT statement for raw data).

How can I use SAS Help to find a list of informats?

I have 4 character variables w/ time values (e.g. 11:00 PM, 1:00PM). When I read them into SAS (Using PROC IMPORT from an Excel file) they are assigned different formats and informats. The first 2 variables are assigned an informat of $21. The next 2 are assigned an informat of $11 (See attachment).

In a PROC PRINT (See attachment), the values that have a space between the numbers and the AM/PM appear as long strings with a single digit followed by multiple digits following the decimal point.

For example, 10:00 AM (Space) appears as 0.916666667, while 10:00AM (No space) appears as 10:00AM.

How should I assign informats for these variables so that the values appear consistent. Thanks for you help!

Re: Using informats for time data

Reeza — Wed, 09 Mar 2016 17:13:33 GMT

Excel doesn't have fixed structures and using Proc Import you're allowing SAS to guess the types. Unfortunately Proc Import with Excel doesn't allow specification of the data types.

If you want full control, you'll need to export the file to a CSV and import the data using a data step with the input and informat statements.

You could process the file manually after the Proc Import but then the next time, if the file structure changes a little, you may not get the exact same import and your code won't work anymore.

Re: Using informats for time data

_maldini_ — Wed, 09 Mar 2016 17:38:59 GMT

@Reeza Thanks. How might I process the data manually in this situation?

Re: Using informats for time data

_maldini_ — Thu, 10 Mar 2016 18:17:57 GMT

@Reeza Could offer some feedback here?

I have saved the Excel file as a CSV. I am attempting to use the following code:

data test;
infile "/folders/myfolders/..." dlm=",";
input _300101 TIMEw. _300102 TIMEw. _300201 TIMEw. _300202 TIMEw.;
run;

proc print data=test;
run;

I am getting an "Invalid data" error for each variable (See attached screen shot).

Thanks!

Re: Using informats for time data

Reeza — Thu, 10 Mar 2016 18:15:31 GMT

@_maldini_ Please post the data as text, I can't put images into SAS and I don't want to type things out.

I would suggest removing the format from the input statement and specifying and INFORMAT instead.

In fact, I would use proc import to see the generated code from the log, and then modify that since it's a bit easier to understand the structure required for a data step import.

Re: Using informats for time data

_maldini_ — Thu, 10 Mar 2016 18:27:53 GMT

@Reeza I just uploaded the data.

I thought I WAS specifying an INFORMAT!

When I run the PROC IMPORT, I receive the following error message:

"NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to WORK.PARMS.PARMS.SLIST.
Unable to sample external file, no data in first 5 records.
ERROR: Import unsuccessful. See SAS Log for details."

proc import
datafile= "/folders/myfolders...csv" 
out=test;
run;

Re: Using informats for time data

Reeza — Thu, 10 Mar 2016 18:29:40 GMT

Post your full code that generates the error. Your text file errors out for me, please post the data into a message.

Re: Using informats for time data

_maldini_ — Thu, 10 Mar 2016 18:41:51 GMT

data test_data;
infile "/folders/myfolders/XXXX/XXXX/! Datasets/Original Datasets/XXXX Complete A4 Data COPY.csv" dlm=",";
input _300101 _300102 _300201 _300202;
run;

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 55         
 56         data test_data;
 57         infile "/folders/myfolders/XXXX/XXXX/! Datasets/Original Datasets/XXXX Complete A4 Data COPY.csv" dlm=",";
 58         input _300101 _300102 _300201 _300202;
 59         run;
 
 NOTE: The infile "/folders/myfolders/XXXX/XXXX/! Datasets/Original Datasets/XXXX Complete A4 Data COPY.csv" is:
       Filename=/folders/myfolders/XXXX/XXXX/! Datasets/Original Datasets/XXXX Complete A4 Data COPY.csv,
       Owner Name=root,Group Name=vboxsf,
       Access Permission=-rwxrwx---,
       Last Modified=10Mar2016:09:52:08,
       File Size (bytes)=226
 
 NOTE: Invalid data for _300101 in line 1 1-7.
 NOTE: Invalid data for _300102 in line 1 9-15.
 NOTE: Invalid data for _300201 in line 1 17-23.
 NOTE: Invalid data for _300202 in line 1 25-39.
 RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      
 
 1   CHAR  _300101,_300102,_300201,_300202.8:00 PM,10:00 PM,4:00 AM,9:00 AM.1:00 AM,2:00 AM,10:00 AM,12:00 PM.1
     ZONE  5333333253333332533333325333333033332542333332542333324423333244033332442333324423333324423333325403
     NUMR  F300101CF300102CF300201CF300202D8A0000DC10A0000DC4A0001DC9A0001DD1A0001DC2A0001DC10A0001DC12A0000DD1
 
      101  2:00AM,11:30AM,8:00AM,6:30AM.12:00 AM,2:00 AM,6:00 AM,11:00 AM.7:00AM,9:00AM,11:00PM,11:00PM.12:00 A
     ZONE  3333442333334423333442333344033333244233332442333324423333324403333442333344233333542333335403333324
     NUMR  2A001DC11A301DC8A001DC6A301DD12A0001DC2A0001DC6A0001DC11A0001DD7A001DC9A001DC11A000DC11A000DD12A0001
      201  M,12:00 AM,6:30 AM,8:00 AM 226
 _300101=. _300102=. _300201=. _300202=. _ERROR_=1 _N_=1
 NOTE: 1 record was read from the infile "/folders/myfolders/XXXX/XXXX/! Datasets/Original Datasets/XXXX Complete A4 Data 
       COPY.csv".
       The minimum record length was 226.
       The maximum record length was 226.
 NOTE: The data set WORK.TEST_DATA has 1 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       cpu time            0.02 seconds

proc import
datafile= "/folders/myfolders/XXXX/XXXX/! Datasets/Original Datasets/XXXX Complete A4 Data COPY.csv" 
out=test_import REPLACE;
run;


 *Time program.sas  Importing Datasets.sas 
CODE LOG RESULTS

Errors, Warnings, Notes
 Errors (1)
 Warnings
 Notes (2)
 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 55         
 56         proc import
 57         datafile= "/folders/myfolders/XXXX/XXXX/! Datasets/Original Datasets/XXXX Complete A4 Data COPY.csv"
 58         out=test_import;
 59         run;
 
 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
 WORK.PARMS.PARMS.SLIST.
 Unable to sample external file, no data in first 5 records.
 ERROR: Import unsuccessful.  See SAS Log for details.
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.06 seconds
       cpu time            0.04 seconds
       
 60         
 61         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 73

_300101,_300102,_300201,_300202 8:00 PM,10:00 PM,4:00 AM,9:00 AM 1:00 AM,2:00 AM,10:00 AM,12:00 PM 12:00AM,11:30AM,8:00AM,6:30AM 12:00 AM,2:00 AM,6:00 AM,11:00 AM 7:00AM,9:00AM,11:00PM,11:00PM 12:00 AM,12:00 AM,6:30 AM,8:00 AM

Re: Using informats for time data

Reeza — Thu, 10 Mar 2016 18:44:51 GMT

Try adding the DBMS

proc import
datafile= "/folders/myfolders/XXXX/XXXX/! Datasets/Original Datasets/XXXX Complete A4 Data COPY.csv" 
DBMS=CSV 
out=test_import REPLACE;
run;

Re: Using informats for time data

_maldini_ — Thu, 10 Mar 2016 18:59:41 GMT

Similar error...

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
55
56 proc import
57 datafile= "/folders/myfolders/xxxx/xxxx/! Datasets/Original Datasets/xxxxx Complete A4 Data COPY.csv"
58 dbms=csv
59 out=test_import REPLACE;
60 run;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
Unable to sample external file, no data in first 5 records.
ERROR: Import unsuccessful. See SAS Log for details.

Re: Using informats for time data

Reeza — Thu, 10 Mar 2016 19:31:08 GMT

Check your file. SAS thinks its empty.

Re: Using informats for time data

_maldini_ — Thu, 10 Mar 2016 20:02:58 GMT

It's not empy. The data is there:

_300101,_300102,_300201,_300202, 8:00 PM,10:00 PM,4:00 AM,9:00 AM 1:00 AM,2:00 AM,10:00 AM,12:00 PM 12:00AM,11:30AM,8:00AM,6:30AM 12:00 AM,2:00 AM,6:00 AM,11:00 AM 7:00AM,9:00AM,11:00PM,11:00PM 12:00 AM,12:00 AM,6:30 AM,8:00 AM