Importing csv file to sas - with datetime variable format

[devon59]

Hi, I am trying to use the following code to import data from csv file to sas but the two fields which I want to appear in sas as datetime format got imported with just . value. How do I fix this code? The final product that I want is the fields Depositdate and ReceiptDate be in datetime format in sas dataset. Thank you!

data WORK.Source ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'C:\VFW\ResponseFile.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat VFWDatabase $10. ;
informat NameID 11. ;
informat ProjectNumber $4. ;
informat DepositDate ;
informat ReceiptDate ;
informat DonationAmount DOLLAR11.2 ;
format VFWDatabase $10. ;
format NameID 11. ;
format ProjectNumber $4. ;
format DepositDate ;
format ReceiptDate ;
format DonationAmount DOLLAR11.2 ;
input
VFWDatabase $
NameID
ProjectNumber $
DepositDate
ReceiptDate
DonationAmount
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;

[sbb]

You need to share more about the input-fields in their native format, and then consider what SAS INFORMAT(s) may apply to the field as you have it in your input file. Suggest that you might add a LIST; command or some additional PUTLOG _ALL_; diagnostics to your DATA step to help describe the problem in more detail.

Scott Barry
SBBWorks, Inc.

[sbb]

Also, what is the exact format of the fields you have identified as datetime - your INFORMAT definition must declare these variable as such, presuming they are in fact correct in format. Your INFORMAT statements for these variables do not declare the variables as being read with any particular INFORMAT, so you should expect to see the missing value condition otherwise.

Scott Barry
SBBWorks, Inc.

About SAS Date, Time, and Datetime Values:
http://support.sas.com/onlinedoc/913/getDoc/en/lrcon.hlp/a002200738.htm

[devon59]

This is the code I got from log when I used proc import wizard. It automatically make those 2 dates to be in yymmdd10. format. However, I need to somehow change yymmdd10. format to datetime format. How do I do that? Thank you.
*********************************************************************************
data WORK.a ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'C:\VFW\ResponseFile.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat VFWDatabase $5. ;
informat NameID best32. ;
informat ProjectNumber $6. ;
informat DepositDate yymmdd10. ;
informat ReceiptDate yymmdd10. ;
informat DonationAmount ;
format VFWDatabase $5. ;
format NameID best12. ;
format ProjectNumber $6. ;
format DepositDate yymmdd10. ;
format ReceiptDate yymmdd10. ;
format DonationAmount ;
input
VFWDatabase $
NameID
ProjectNumber $
DepositDate
ReceiptDate
DonationAmount
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;

[sbb]

Review the use of the DHMS function to assign a SAS numeric DATETIME variable. You can code your SAS numeric DATE variable as argument #1, then code zero for arguments #2, #3 and #4.

Scott Barry
SBBWorks, Inc.

[deleted_user]

there are informats that can read datetime strings in rawdata like you might have [pre] yyyymmdd hh:mm:ss [/pre] but without a sample record from your input file it is not possible to declare which. We can suggest you use the testing informat ANYDTDTM.
With some sample records from your input file we can provide a much simpler/better/easier-to-learn-from/more-maintainable/and reliable
load step, and still with SAS.

hth

PeterC

[devon59]

Here's the sample records.

"ID","Project","DepositDate","ReceiptDate","Amount"
202711036,"9999",2009-03-03 00:00:00,2009-02-27 00:00:00,20
203393517,"9999",2009-03-03 00:00:00,2009-02-27 00:00:00,10
203847278,"9999",2009-03-03 00:00:00,2009-02-27 00:00:00,25
200344370,"1638",2009-03-03 00:00:00,2009-02-27 00:00:00,10

Thank you

[data_null__]

I used your data step and data sample. I think your problem is that the variable on the input statement don't match the fields in the file. I removed one of the variables vfwdatabse that appeared to be missing from the data records.

[pre]
data WORK.Source ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile cards /*'C:\VFW\ResponseFile.csv'*/ delimiter = ',' MISSOVER DSD /*lrecl=32767*/ firstobs=2 ;
*informat VFWDatabase $10. ;
informat NameID 11. ;
informat ProjectNumber $4. ;
informat DepositDate ymddttm.;
informat ReceiptDate ymddttm.;
informat DonationAmount comma10.;
*format VFWDatabase $10. ;
format NameID 11. ;
format ProjectNumber $4. ;
format DepositDate datetime.;
format ReceiptDate datetime.;
format DonationAmount DOLLAR11.2 ;
input
/*VFWDatabase $*/
NameID
ProjectNumber $
DepositDate
ReceiptDate
DonationAmount
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
cards;
"ID","Project","DepositDate","ReceiptDate","Amount"
202711036,"9999",2009-03-03 00:00:00,2009-02-27 00:00:00,20
203393517,"9999",2009-03-03 00:00:00,2009-02-27 00:00:00,10
203847278,"9999",2009-03-03 00:00:00,2009-02-27 00:00:00,25
200344370,"1638",2009-03-03 00:00:00,2009-02-27 00:00:00,10
;;;;
run;
proc contents fmtlen varnum;
run;
proc print;
run;
[/pre]

[devon59]

Thank you so much. That works.

[deleted_user]

Devon59

The SAS code on which your posting was based, looked like the style generated internally by PROC IMPORT. Although this works, it is not the easiest to support modify or explain.

Thank you for posting some data.
I know you already have a working solution, so this reply intends only to show how simple a solution can be achieved when you know your raw data layout.[pre]data loaded ;
infile 'your csv file.csv' dsd firstobs= 2 ;
length ID 8 Project $4 ;
format DepositDate ReceiptDate datetime. amount comma12. ;
informat DepositDate ReceiptDate YMDDTTM. ;
input id -- amount ;
run;[/PRE]
Once you have defined or implied the columns of input with LENGTH and (IN)FORMAT statements, you can use the "--" variable list notation to simplify the input statement.

Since your example data shows no time values, you may prefer the date-time format DTDATE9.

I hope this example might help the next time you need to read a CSV file.


PeterC