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 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.
@Reeza Thanks. How might I process the data manually in this situation?
@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!
@_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.
@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;
Post your full code that generates the error. Your text file errors out for me, please post the data into a message.
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
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;
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.