convert alpha to numeric without creating a new var

[podarum]

This is not working for me.. but I have data that is both alpha and numeric.  I'm trying to make it all numeric. (it's alot of data and it has been imported with many variables mixed in numeric and alpha)..

eg.

data HAVE;

  input VAR1 $ VAR2 ..VAR12 $ .... VAR23 ;

  datalines;

1  34 ....  46

2  45 ....  57

;

WANT all of them to be numeric.

[Tom]

The real solution is to fix the IMPORT step to not do that.  What is the source format that is being input? How consistent are the files? Could you instead use a program instead of trying to use an import tool?

To convert the type you will need to create new variables.

One possible quick way to convert would be to dump the current data to a CSV file and then reread it.

filename dump temp;

data _null_;

  set sashelp.class;

  file dump dsd dlm=',' lrecl=32000;

  put (_all_) (:);

run;

data want ;

  length var1-var5 8;

  infile dump dsd dlm=',' lrecl=32000 truncover ;

  input var1-var5 ;

run;

[podarum]

I'm getting a large file in Excel with dates as the variables (about 50 dates), and importing it everry month.  I can convert them in Excel, but wanted it to automate it in SAS instead.

[art297]

The following is a way to convert your example data, and it could easily be adjusted to account for dates, but it was based on your variables being named like in your example:

data have;

  input var1 $ var2 var3 $ var4 var5 $;

  cards;

1 2 3 4 5

;

proc sql noprint;

  select name,name||"=_"||name,

    case type

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

      else name||"=_"||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 have (drop=_:);

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

  array var(&sqlobs.);

  &resets;

run;

[Reeza]

Ideally do it in your import step rather than a processing data step.

If its dates you're also better off show some sample data that's dates and how the import isn't working properly, ie where does it import as text.

[podarum]

Import works fine, it's just the data I receive in the Excel format has half of it in numeric form and the other in alpha form, but they're all numbers.  I can make the changes in Excel before hand, but wanted a way for SAS to handle it.

[LarryWorley]

Do you have access to pc files?  If so, there are switches on excel libname statement to do what you need.

[podarum]

Larry can you elaborate on that?, it sounds like a solution. But don't know what you mean.   I have access to the files that are located on mu PC

[LarryWorley]

I should have been more specific.

Do you have the SAS/Access to Interface PC Files installed on your pc?   If you are not sure, then run "proc setinit; run;' and see if that is listed in the log as a product.

[podarum]

Yes, I have it.

SAS/ACCESS  Interface to PC Files

[LarryWorley]

Then, assuming you have your data in an actual Excel file and not CSV file, then try using excel libname.  I expect the date issue to be handled transparently.

Something like:

libname my_excel excel "my_excel_file_name_with_full_path.xls' ;

* use to get list of sheet and range names. Sheet names will end in '$' character.  ;

proc contents data = my_excel._all_ ;

*Assuming your data is on sheet1.  Using name string to specify data set name with non-alphabetic characters (includes space) ;

data my_data ;

  set my_excel.'sheet1$'n ;

run ;

[podarum]

I tried this , and still got alpha.

ERROR: All variables in array list must be the same type, i.e., all numeric or character.

ERROR: All variables in array list must be the same type, i.e., all numeric or character.

ERROR: All variables in array list must be the same type, i.e., all numeric or character.

NOTE: Character values have been converted to numeric values at the places given by:

(Line):(Column).

528:133

NOTE: Numeric values have been converted to character values at the places given by:

(Line):(Column).

528:147

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.CONDO_TEST2 may be incomplete. When this step was stopped there

were 0 observations and 122 variables.

WARNING: Data set WORK.CONDO_TEST2 was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

[Reeza]

I think there might be some misunderstanding about your question.

You have an excel file with about 50 variables that are in a date format. About half import as numbers and half import as character? Is that correct?

I'd also guess that the dates that aren't importing are in some format such as day/month/year. 

But I'm guessing so you'd need to provide more information on how your file is structured to be helpful.

For these types of problems, there's generally 3 solutions I've seen.

1. Import everything as character and process afterwards in SAS

2. Connect to Excel file using an ODBC connection and explicitly state the data types for each field.

3. Some combination of above, if you know only certain fields will give you problems.

But at any rate the solution becomes highly customized to the particular dataset/issue so without more details its hard to help.

[podarum]

Correct on all your questions. 

[LarryWorley]

Can you attach a file with the first ten lines or so of your spreadsheet?