BookmarkSubscribeRSS Feed
_maldini_
Barite | Level 11

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!


excel.jpginformat.jpgsas.jpg
11 REPLIES 11
Reeza
Super User

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.

 

 

 

_maldini_
Barite | Level 11

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

_maldini_
Barite | Level 11

@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!

 


error log.jpgtime data example.jpg
Reeza
Super User

@_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. 

_maldini_
Barite | Level 11

@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;
Reeza
Super User

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

_maldini_
Barite | Level 11
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

Reeza
Super User

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;
_maldini_
Barite | Level 11

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.

Reeza
Super User
Check your file. SAS thinks its empty.
_maldini_
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1468 views
  • 4 likes
  • 2 in conversation