Help using Base SAS procedures

Import CSV file with date and time

Reply
Contributor
Posts: 27

Import CSV file with date and time

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.

 

Attachment
Super User
Posts: 7,472

Re: Import CSV file with date and time

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 27

Re: Import CSV file with date and time

Thanks 

 

Super User
Posts: 7,472

Re: Import CSV file with date and time

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 Smiley Wink

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,472

Re: Import CSV file with date and time

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Highlighted
Super User
Posts: 19,194

Re: Import CSV file with date and time

@dash you've misunderstood @KurtBremser.

 

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. 

Super User
Super User
Posts: 7,730

Re: Import CSV file with date and time

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.  

Ask a Question
Discussion stats
  • 6 replies
  • 122 views
  • 0 likes
  • 4 in conversation