topic Re: How to import .csv file with date correctly in New SAS User

How to import .csv file with date correctly

mantubiradar19 — Tue, 11 May 2021 12:22:28 GMT

Hello everyone, I'm trying to import the .csv file containing data into SAS. However, the date column is converted to char and format as $12.

Please suggest to me a way import this data correctly into SAS. Here are the first few observations from the.csv file:

Test_ID	Test_date	Test	Test_unit	Test_ref
M00000001	27-05-19	2.92	g/dL	3.8-5.3
M00000001	03-06-19	3.08	g/dL	3.8-5.3
M00000001	05-06-19	3.24	g/dL	3.8-5.3
M00000001	10-06-19	3.51	g/dL	3.8-5.3
M00000001	18-06-19	3.76	g/dL	3.8-5.3
M00000001	13-03-20	3.06	g/dL	3.8-5.3
M00000001	23-03-20	3.4	g/dL	3.8-5.3
M00000001	03-04-20	3.72	g/dL	3.8-5.3
M00000002	11-01-17	3.39	g/dL	3.8-5.3
M00000002	18-01-17	3.53	g/dL	3.8-5.3
M00000003	24-11-16	4.18	g/dL	3.8-5.3
M00000003	07-06-18	4.44	g/dL	3.8-5.3

And here is the import code I have used:

%let path=E:\Lab_tests;
options validvarname=v7;

proc import datafile="&path\Lab_test.csv"
out=Lab_test
dbms=csv REPLACE;
guessingrows=max;
run;quit;

Thanks in advance

Re: How to import .csv file with date correctly

Tom — Tue, 11 May 2021 12:55:16 GMT

If your CSV really looks like what you posted then you should get only one variable because there are no commas there. Are you sure you did not open the file in Excel and copy and paste some of the cells from the Excel spreadsheet that Excel created from the CSV file? Open the CSV file in a text editor. Or just type it to the terminal window. Or view it with the SAS editor. Or a simple data step.

If the file only has 5 variables then skip the PROC IMPORT and just write your own data step to read it.

If you are not sure what style the date values have try using ANYDTDTE informat. But if you really have 2 digit years you might have trouble. And if your know that the data is in M-D-Y or D-M-Y order then MMDDYY or DDMMYY informat instead, otherwise depending on your language settings ANYDTDTE might convert December tenth into twelfth of October.

data Lab_test;
  infile "&path\Lab_test.csv" dsd truncover firstobs=2;
  length Test_ID $10 Test_date 8 Test 8 Test_unit $10 Test_ref $20 ;
  informat test_date anydtdte.;
  format test_date yymmdd10.;
  input test_id -- test_ref;
run;

Re: How to import .csv file with date correctly

mantubiradar19 — Tue, 11 May 2021 13:10:50 GMT

Sorry, earlier I opened the .csv file with excel to view it. Here are the observations as seen using text editor:

"Test_ID","Test_date","Test","Test_unit","test_ref"
"M00000001","2019/05/27","2.92","g/dL","3.8-5.3"
"M00000001","2019/06/03","3.08","g/dL","3.8-5.3"
"M00000001","2019/06/05","3.24","g/dL","3.8-5.3"
"M00000001","2019/06/10","3.51","g/dL","3.8-5.3"
"M00000001","2019/06/18","3.76","g/dL","3.8-5.3"
"M00000001","2020/03/13","3.06","g/dL","3.8-5.3"
"M00000001","2020/03/23","3.40","g/dL","3.8-5.3"
"M00000001","2020/04/03","3.72","g/dL","3.8-5.3"
"M00000002","2017/01/11","3.39","g/dL","3.8-5.3"
"M00000002","2017/01/18","3.53","g/dL","3.8-5.3"
"M00000003","2016/11/24","4.18","g/dL","3.8-5.3"
"M00000003","2018/06/07","4.44","g/dL","3.8-5.3"
"M00000004","2018/01/15","3.59","g/dL","3.8-5.3"
"M00000004","2018/01/21","3.61","g/dL","3.8-5.3"

The dates are in D-M-Y order.

Re: How to import .csv file with date correctly

Tom — Tue, 11 May 2021 13:14:43 GMT

Actually they are in YMD order. PROC IMPORT should handle that fine.

But PROC IMPORT has a "feature" that if every cell is quoted it thinks that means you want to define it as character.

The data step I posted before should work using ANYDTDTE informat. You might want to change to YYMMDD informat instead. That would cause SAS to report date strings that are not in Y-M-D order as errors.

Re: How to import .csv file with date correctly

mantubiradar19 — Tue, 11 May 2021 13:17:27 GMT

data Lab_test;
infile "&path\Lab_test.csv" dsd truncover firstobs=2;
length Test_ID $10 Test_date 8 Test 8 Test_unit $10 Test_ref $20 ;
informat test_date YYMMDD.;
format test_date yymmdd10.;
input test_id -- test_ref;
run;

So, I can import this way?