topic Re: Import CSV file with date and time in SAS Procedures

Import CSV file with date and time

dash — Thu, 05 Oct 2017 13:51:44 GMT

Hello Everyone,

I want to import the CSV file into SAS dataset and save it in a library. The excel file contain around 5 lakhs records.

I wrote the below code but how to capture the time, I have no idea.

Libname Store "/tempo/CSV/project/";

proc import datafile="/tempo/CSV/project/information.csv"
     out=Mydata
     dbms=csv
     replace;
     getnames=yes;
run;

data Store.D_01SEP2017;
set Mydata;
run;

Also If I want to add one sample record with current date, can I use datetime22.

Re: Import CSV file with date and time

Kurt_Bremser — Thu, 05 Oct 2017 14:12:21 GMT

Use a manually written data step, and convert to a SAS datetime value:

data want;
infile cards dlm=',';
input Country :$20. Name $ Age Sex :$1. Registration_string :$20. Rank SeqNo $;
reg_date = input(scan(Registration_string,1,' '),ddmmyy10.);
reg_time = input(scan(Registration_string,2,' '),time5.);
registration = reg_date * 86400 + reg_time;
format registration datetime19.;
drop Registration_string reg_date reg_time;
cards;
India,AA12,21,M,28/06/2017 14:09,1,A-001
Bhutan,AA123,21,F,28/06/2017 14:09,2,A-002
Srilanka,AA234,21,M,19/09/2017 23:59,3,A-003
US,AA345,21,F,26/09/2017 04:14,4,A-004
UK,AA456,21,M,28/09/2017 03:20,5,A-005
USSR,AA567,21,F,19/09/2017 23:59,6,A-006
China,AA678,21,M,21/09/2017 03:23,7,A-007
Japan,AA789,21,F,30/06/2017 16:34,8,A-008
SK,AA900,21,M,30/06/2017 16:34,9,A-009
;
run;

Re: Import CSV file with date and time

dash — Thu, 05 Oct 2017 14:15:58 GMT

Thanks KurtBremser for your support.

But my excel file contain more than 5 lakhs records. In that scenario, I think proc import is the suitable procedure.

It is not possible to write data for all 5 lakhs records

Re: Import CSV file with date and time

RW9 — Thu, 05 Oct 2017 14:22:32 GMT

Firstly, and most importantly, CSV is not Excel!!

Secondly your letting SAS guess what your import should look like - i.e. the proc import. If you want data in a specific format then write the code yourself, e.g.:

data want;
  infile "...information.csv" dlm=",";
  length ...;
  informat ...;
  format ...;
  input...;
run;

So you specify the length informat format, on how to read in the text data.

Re: Import CSV file with date and time

Kurt_Bremser — Thu, 05 Oct 2017 14:25:58 GMT

In fact, a data step is ALWAYS the better solution, as you have full control over the process (while proc import relies on guesses).

The number of records does not matter, apart from issues with space in the target library. Again, you have better control over the space needed with a data step.

BTW, when importing a csv file, proc import does write a data step on its own and runs that. You can find this data step in the SAS log.

500.000 observations of the structure you gave will need < 50 MB, trivial with SAS. That's not even "small", that's negligibly close to nothing 😉

Re: Import CSV file with date and time

Kurt_Bremser — Thu, 05 Oct 2017 14:27:26 GMT

PS I used cards; in the data step only for easier illustration. Drop the cards; section and add your original infile statement to read from the file.

Re: Import CSV file with date and time

Reeza — Thu, 05 Oct 2017 15:02:39 GMT

@dash you've misunderstood @Kurt_Bremser.

You can point a data step to a file rather than use CARDS as he did.

In fact, look at your log for the code and add in the components you need.

It's more work to download your file and work with it, so a data step example with CARDS is easier to post here.