BookmarkSubscribeRSS Feed
Haikuo
Onyx | Level 15

Try this:

xxx,xxx

aaa, 12.20

bbb, 12.2

ccc,12.22

Haikuo

maggi2410
Obsidian | Level 7

I dont know why..but it still isnt working..

Haikuo
Onyx | Level 15

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

maggi2410
Obsidian | Level 7

My excel already has a row for the column names. thats why i used yes.

Haikuo
Onyx | Level 15

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

Haikuo
Onyx | Level 15

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

art297
Opal | Level 21

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:

xy
xxxxxx
aaa12.20
bbb12.2
ccc12.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;

maggi2410
Obsidian | Level 7

Thanks everybody for your valuable answers.

  it still doesnt work for me

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.

Haikuo
Onyx | Level 15

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

PGStats
Opal | Level 21

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

PG
art297
Opal | Level 21

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 25 replies
  • 4353 views
  • 0 likes
  • 5 in conversation