Try this:
xxx,xxx
aaa, 12.20
bbb, 12.2
ccc,12.22
Haikuo
I dont know why..but it still isnt working..
Getnames=NO? if using YES, then the first record will be variable names, then the mixed option will not work, as the rest of data are numeric. Or add another row of xxx,xxx .
Haikuo
My excel already has a row for the column names. thats why i used yes.
You got me. As long as data type is mixed in the same column in the first couple of rows, and 'mixed=yes', proc import is supposed to import whatever as is in character format. It works for me on SAS 9.2, Excel 2003, winxp sp4.
Haikuo
I have attached my test excel file, It works for me using your code. you may test it out on your system and hopefully figure out what is going on.
Haikuo
I sit corrected! Proc import ST|ILL doesn't get it right.
However, I imported the file using the code from the paper I suggested (including the line that you wouldn't really didn't need to add), and I got the following (correct) result:
x | y |
xxx | xxx |
aaa | 12.20 |
bbb | 12.2 |
ccc | 12.22 |
The additional dde code I included, and the macro variable settings I used instead of the defaults shown in the paper, were:
filename ddecmds dde "excel|system";
options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\Office11\EXCEL.exe"';
data _null_;
z=sleep(3); /* wait 3 seconds for Excel to start */
run;
data _null_; /* talk to DDE, no output data */
file DDEcmds;
put '[open("c:\art\import.xls")]';
x=sleep(3); /* wait 3 seconds for it to open */
run;
data _null_;
file DDEcmds;
put '[workbook.activate("Sheet1")]';
put '[select("C1:C2")]';
put '[copy()]';
run;
data _null_;
file DDEcmds;
put '[error(false)]';
put '[quit()]';
run;
options NOQUOTELENMAX;
options datestyle=mdy;
filename clippy clipbrd;
filename revised temp;
%let hrows=1;
%let first_data_row=2;
%let var_formats=1-2~$8.;
%let var_informats=1-2~$8.;
%let transpose=NO;
%let columns=;
%let rows=;
%let data_form=;
%let spaces=" ";
%let var_renames=;
%let var_labels=;
%let var_share=;
%let var_prefix=;
%let var_suffix=;
%let var_drop=;
%let var_upcase=;
%let var_missing=;
%let var_units=;
%let guessingrows=;
%let outfile=want;
Thanks everybody for your valuable answers.
this solution also wont work for me. Firstly, because mine is a UNIX server. And DDE isnt working on it. Also, as I got to automate this process of excel to tab delimited file conversion without any data change, I can't add rows to even duplicate copy of excel(coz that would again involve reading excel which would again cause the same issue).
Again, my task is to convert the excel(with all possible values) to tab delimited txt file AUTOMATICALLY.
Not sure if it is relevant, but I have tried this little vb script before, of course on Winxp platform. The execution(double click) of this script will output every .xls file within the same folder into tab delimited txt file under subfolder 'output'.
I am no expert on vb, but I think there is a good chance that you can write your own vb script and call within SAS as well.
Haikuo
As was alluded before, you could use GETNAMES=NO and MIXED=YES to read in all the data as text and then convert only the columns that you want to numbers, using a datastep with
SET myTempDataSet(FIRSTOBS=2);
myVar = input(F3, best12.);
... etc.
not elegant, I know, but I find that importing data from a non-database to a database is very often messy.
Hth
PG
It may be a moot point, since I think you said that you couldn't add an upfront record but, if you could, the method I had originally suggested would have worked if you used the excel rather than the xls engine. i.e.:
PROC IMPORT OUT= tfl
DATAFILE= "c:\art\import.xls" DBMS=excel REPLACE;
sheet="Sheet1"; GETNAMES=YES; MIXED=yes;
RUN;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.