topic convert alpha to numeric without creating a new var in SAS Procedures

convert alpha to numeric without creating a new var

podarum — Tue, 17 Jan 2012 20:02:13 GMT

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.

convert alpha to numeric without creating a new var

Tom — Tue, 17 Jan 2012 20:51:15 GMT

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;

convert alpha to numeric without creating a new var

podarum — Tue, 17 Jan 2012 20:57:37 GMT

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.

convert alpha to numeric without creating a new var

art297 — Tue, 17 Jan 2012 21:12:41 GMT

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;

convert alpha to numeric without creating a new var

Reeza — Tue, 17 Jan 2012 21:45:22 GMT

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.

convert alpha to numeric without creating a new var

LarryWorley — Tue, 17 Jan 2012 22:14:17 GMT

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

convert alpha to numeric without creating a new var

podarum — Wed, 18 Jan 2012 03:21:23 GMT

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

convert alpha to numeric without creating a new var

podarum — Wed, 18 Jan 2012 13:40:16 GMT

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.

convert alpha to numeric without creating a new var

LarryWorley — Wed, 18 Jan 2012 14:11:25 GMT

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.

convert alpha to numeric without creating a new var

podarum — Wed, 18 Jan 2012 14:15:10 GMT

Yes, I have it.

SAS/ACCESS Interface to PC Files

Re: convert alpha to numeric without creating a new var

LarryWorley — Wed, 18 Jan 2012 16:08:14 GMT

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 ;

Re: convert alpha to numeric without creating a new var

podarum — Wed, 18 Jan 2012 17:03:35 GMT

I tried this , and still got alpha.

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

Re: convert alpha to numeric without creating a new var

LarryWorley — Wed, 18 Jan 2012 17:32:45 GMT

Please examine proc contents for the input table.

What columns are alpha where you expect to be numeric? Examine those columns to see if you have some kind of data error. For example, a single value of 'unknown date' in the excel spreadsheet would cause that column to be read as character and not datetime.

Re: convert alpha to numeric without creating a new var

Reeza — Wed, 18 Jan 2012 17:38:06 GMT

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.

Re: convert alpha to numeric without creating a new var

podarum — Wed, 18 Jan 2012 18:09:15 GMT

Correct on all your questions.

Re: convert alpha to numeric without creating a new var

LarryWorley — Wed, 18 Jan 2012 18:16:24 GMT

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

Re: convert alpha to numeric without creating a new var

art297 — Wed, 18 Jan 2012 18:24:27 GMT

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;

Re: convert alpha to numeric without creating a new var

podarum — Wed, 18 Jan 2012 18:31:19 GMT

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

Re: convert alpha to numeric without creating a new var

Reeza — Wed, 18 Jan 2012 18:31:42 GMT

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;

Re: convert alpha to numeric without creating a new var

podarum — Wed, 18 Jan 2012 18:32:41 GMT

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..