BookmarkSubscribeRSS Feed
mantubiradar19
Quartz | Level 8

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

 

4 REPLIES 4
Tom
Super User Tom
Super User

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;
mantubiradar19
Quartz | Level 8

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. 

Tom
Super User Tom
Super User

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.

 

mantubiradar19
Quartz | Level 8
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?

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 763 views
  • 0 likes
  • 2 in conversation