10-05-2017 09:51 AM
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.
10-05-2017 10:12 AM
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;
10-05-2017 10:15 AM
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
10-05-2017 10:25 AM
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
10-05-2017 10:27 AM
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.
10-05-2017 11:02 AM
10-05-2017 10:22 AM
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.