convert alpha to numeric without creating a new var

[podarum]

Sure...How do you attach files (Excel) in this forum?

[LarryWorley]

By using advanced editor and attaching an excel 97-2003 workbook (I think).

Attach files functions says xlsx files are not allowed but does not mention xls files.

I attached test.xls as a test of this.

[podarum]

Here it is...

[Reeza]

The dates are your variable names, sas variable names cannot start with a number so you get the _ instead at the front but otherwise the data will import fine from your sample data.

What exactly is the issue?

[podarum]

I know about the _ in dates.. try importing it and tell me if all you got back were numeric values?   I had to delete some data, so I might not be including the data that's in question.. Let me try again.

[LarryWorley]

I can import with no problem.  But this does not look the same as example contents you sent earlier.  Why the difference?

[podarum]

This should be better...

[LarryWorley]

I still see no issue importing.  All columns are numeric.

Can you send actual code used for import and the log for the import?

[Reeza]

I get the character values, because there are a lot of missing observations and when SAS first reads the dataset it doesn't know how to set the column type so it sets it to character rather than numeric.

Change your guessingrows option in Excel (Macro for that here: http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0811b&L=sas-l&D=1&O=A&P=41923)

or see a solution posted by Art here:

http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0802b&L=sas-l&D=1&O=A&P=15595

or use the solution Art posted as that will work.

[LarryWorley]

There may be another solution for this if you are running in 9.2 or above.  There is now a parameter called, TypeGuessRows, in the Windows OS registry, not the SAS registry.  This setting needs to be changed from 8 to 0.  This allows 16384 rows to be scanned for guessing.  I have had this reset on my system which is running 9.2. 

See either the 9.2 or 9.3 SAS/Access Interface to PC files for documentation on this.

BTW, I read you lastest file with no issue.

[podarum]

Here's the code :

PROC IMPORT Datafle = 'C:\TEMP\SAS_TEST2.xls'

OUT=SAS_TEST REPLACE; SHEET='TEST';run; 

And the 'View Columns' is attached, so you can see I get some vars that are nums and others that are alphas.

LOG :

209 PROC IMPORT Datafile = 'C:\TEMP\SAS_TEST2.xls'

210 OUT=SAS_TEST REPLACE;

210! SHEET='TEST';run;

NOTE: WORK.SAS_TEST data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 1.09 seconds

cpu time 0.82 seconds

[art297]

I'd still recommend the approach I originally suggested, just modified to account for your actual variable names.  e.g.:

data have;

  input (ShapeKey GeoType _00110-_00112) ($)

    _00304-_00308;

  cards;

1 1 1 2 3 4 5 6 7 8

;

proc sql noprint;

  select name,name||"=old"||name,

    case type

      when "char" then name||"=input(old"||name||",12.);"

      else name||"=old"||name||";"

    end

      into :junk separated by " ",

           :renames separated by " ",

           :resets separated by " "

        from dictionary.columns

          where libname="WORK" and

          memname="HAVE"

          order by name

  ;

quit;

data want (drop=old:);

  set have (rename=(&renames.));

  &resets;

run;

[Reeza]

That does work .

Add in a format for your dates if you'd like as well, just to make sure:

data want (drop=old:);

  set have (rename=(&renames.));

  &resets;

format _0: date9.;

run;

[podarum]

I agree Art, the problem is that I have about 15 files that I have to do this to. And I was hoping to find a less coding way around it..

[Reeza]

Wrap Art's code in a macro and call it with the dataset name from import, dataset name you want and call it 15 times.

% macro convert2number(dsetin, dsetout);

proc sql noprint;

  select name,name||"=old"||name,

    case type

      when "char" then name||"=input(old"||name||",12.);"

      else name||"=old"||name||";"

    end

      into :junk separated by " ",

           :renames separated by " ",

           :resets separated by " "

        from dictionary.columns

          where libname="WORK" and

          memname=upcase("&dsetin")

          order by name

  ;

quit;

data &dsetout (drop=old:);

  set &dsetin (rename=(&renames.));

  &resets;

run;

%mend;

%convert2number(tablein1, tableout1);

%convert2number(tablein2, tableout2);