BookmarkSubscribeRSS Feed
devon59
Calcite | Level 5
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;
9 REPLIES 9
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Calcite | Level 5
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Calcite | Level 5
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__
Jade | Level 19
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
Calcite | Level 5
Thank you so much. That works.
deleted_user
Not applicable
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

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
  • 9 replies
  • 22644 views
  • 1 like
  • 4 in conversation