topic Re: importing data when variable has different data types in excel sheets? in SAS Programming

importing data when variable has different data types in excel sheets?

praveenkotte — Wed, 20 Jul 2016 12:42:05 GMT

I need to extract this data from excel file. the thing is, in the below variables the date variable consists of both numeric and charater data type. while iam reading into sas ,i am getting only few records. i should get all records into sas data set.

could you please help me ?

the below attchement consists of sample of my data.

thanks in advance

Date	particulars	chequeNO/OrderNo
23/06/06	received	234554
11/8/2006	received	798242
12/8/2006	received	985795
20/08/06	received	298489
26/08/06	received	988323
28/08/06	received	248927
31/08/06	received	598332
5/9/2006	received	284729
3/9/2006	received	287492
15/9/2006	received	279872
1/9/2006	received	258202
2/9/2006	received	928590
1/11/2006	received	209842
5/12/2006	received	595972
5/12/2006	received	902892
20/12/06	received	298297

Re: importing data when variable has different data types in excel sheets?

Cynthia_sas — Wed, 20 Jul 2016 12:57:14 GMT

Hi:
What code have you tried? Easiest way would be to fix the format of the column in Excel to be a type of date and then if the only character value is the column header, PROC IMPORT or the LIBNAME engine should be able to read the date value correctly.

cynthia

Re: importing data when variable has different data types in excel sheets?

praveenkotte — Wed, 20 Jul 2016 13:11:18 GMT

i've tried these two codes. but instead of using libname statement, i should pull out the data using proc import or else proc sql.

proc sql;
connect to excel(path="C:\Users\siddiq\Desktop\Book1.xls");
create table tab as
select * from connection to excel
(
select * from [sheet1$]
);
disconnect from excel;
quit;

proc import datafile="C:\Users\siddiq\Desktop\book1.xls"
out=dd
dbms=excel
replace;
run;

Re: importing data when variable has different data types in excel sheets?

rogerjdeangelis — Thu, 21 Jul 2016 01:15:44 GMT

OT Because I present a SAS, WPS and R Solution

If interested see

https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;47be38ca.1607c