BookmarkSubscribeRSS Feed
Lucas1
Calcite | Level 5

I am attempting to import a CSV file into SAS Studio, however all dates (formatted as 9/11/2012 in the csv) are being converted to the single digit numbers. If I cut and insert a row at the top of the data then it imports it properly. Please help, I don't understand why having the second row (1st row has my variable names) without a date causes this error. Thanks!

 

Here is the code for my import:

%web_drop_table(WORK.IMPORT);
FILENAME REFFILE '/folders/myfolders/DP_NUTR_BMI-REQUESTED-20180227-1.csv';
PROC IMPORT DATAFILE=REFFILE
DBMS=CSV
OUT=WORK.IMPORT;
GETNAMES=YES;

RUN;
PROC CONTENTS DATA=WORK.IMPORT; RUN;
%web_open_table(WORK.IMPORT);

8 REPLIES 8
Reeza
Super User

What is the type and format of the date variable you think is being read in correctly?

Post the output from the PROC CONTENTS if you can. 

 

If it's number then it may be correct and you may need to apply a format.

 

data import_with_date;
set import;

format DATE_VARIABLE yymmdd10.;

run;

@Lucas1 wrote:

I am attempting to import a CSV file into SAS Studio, however all dates (formatted as 9/11/2012 in the csv) are being converted to the single digit numbers. If I cut and insert a row at the top of the data then it imports it properly. Please help, I don't understand why having the second row (1st row has my variable names) without a date causes this error. Thanks!

 

Here is the code for my import:

%web_drop_table(WORK.IMPORT);
FILENAME REFFILE '/folders/myfolders/DP_NUTR_BMI-REQUESTED-20180227-1.csv';
PROC IMPORT DATAFILE=REFFILE
DBMS=CSV
OUT=WORK.IMPORT;
GETNAMES=YES;

RUN;
PROC CONTENTS DATA=WORK.IMPORT; RUN;
%web_open_table(WORK.IMPORT);


 

Lucas1
Calcite | Level 5

SAS Studio

4
DECEASED_DATEChar1$1.$1.

 

When I run the coding you provide this is the error message I get:

SAS Studio

65 format DECEASED_DATE mmddyy10.;
_________
484
NOTE 484-185: Format $MMDDYY was not found or could not be loaded.
 
Coding used:
data import_with_date;
set import;
format DECEASED_DATE mmddyy10.;
run;
Kurt_Bremser
Super User

The best way to read a csv file into SAS is a data step written according to the specification accompanying the file.

Copy the data step code from the log of the successful test, and use it after adapting.

Lucas1
Calcite | Level 5

Could you elaborate on the "specification accompanying the file"? Not sure if I understand you completely.

 

The only difference from the successful import and unsuccessful is in the CSV file where I put a date in the first row of data. 

 

Thanks,

Lucas

Kurt_Bremser
Super User

A data file without a description of column attributes is mostly useless for meaningful work, so you should resort to the description you got with the file.

As a helper, copy the data step code from the log of the successful run of proc import with the manipulated data.

Lucas1
Calcite | Level 5

Is this what you're referring to?

 

SAS Studio

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat1
DECEASED_DATENum8MMDDYY10.MMDDYY10.
Tom
Super User Tom
Super User

@Lucas1 wrote:

Could you elaborate on the "specification accompanying the file"? Not sure if I understand you completely.

 

The only difference from the successful import and unsuccessful is in the CSV file where I put a date in the first row of data. 

 

Thanks,

Lucas


A CSV file does not supply any metadata data (other than the optional header row) about the data that it contains.

If someone sends you a CSV file they should also supply you with information that clearly describes what each column contains.  Without that you will need to guess at the meaning type and length for each column.

 

All proc import is doing is guessing what is in the file and then generating a data step to read it. 

 

If you know what is in the file then just write the data step yourself.  Even if you don't know what is in the file you should still be able to do a much better job than PROC IMPORT can at guessing what is in the file .

 

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
  • 8 replies
  • 1460 views
  • 0 likes
  • 4 in conversation