Help using Base SAS procedures

convert alpha to numeric without creating a new var

Reply
Super Contributor
Posts: 395

convert alpha to numeric without creating a new var

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.

Super User
Super User
Posts: 6,499

convert alpha to numeric without creating a new var

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_) (Smiley Happy;

run;

data want ;

  length var1-var5 8;

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

  input var1-var5 ;

run;

Super Contributor
Posts: 395

convert alpha to numeric without creating a new var

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.

PROC Star
Posts: 7,363

convert alpha to numeric without creating a new var

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=_Smiley Happy;

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

  array var(&sqlobs.);

  &resets;

run;

Super User
Posts: 17,801

convert alpha to numeric without creating a new var

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.

Super Contributor
Posts: 395

convert alpha to numeric without creating a new var

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.

Frequent Contributor
Posts: 129

convert alpha to numeric without creating a new var

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

Super Contributor
Posts: 395

convert alpha to numeric without creating a new var

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

Frequent Contributor
Posts: 129

convert alpha to numeric without creating a new var

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.

Super Contributor
Posts: 395

convert alpha to numeric without creating a new var

Yes, I have it.

SAS/ACCESS  Interface to PC Files

Frequent Contributor
Posts: 129

Re: convert alpha to numeric without creating a new var

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 ;

Super Contributor
Posts: 395

Re: convert alpha to numeric without creating a new var

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)Smiley SadColumn).

528:133

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

(Line)Smiley SadColumn).

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

Super User
Posts: 17,801

Re: convert alpha to numeric without creating a new var

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.

Super Contributor
Posts: 395

Re: convert alpha to numeric without creating a new var

Correct on all your questions. 

View Columns.png

Frequent Contributor
Posts: 129

Re: convert alpha to numeric without creating a new var

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

Ask a Question
Discussion stats
  • 33 replies
  • 857 views
  • 0 likes
  • 8 in conversation