BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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.

33 REPLIES 33
Tom
Super User Tom
Super User

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
Quartz | Level 8

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
Opal | Level 21

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
Super User

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
Quartz | Level 8

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
Fluorite | Level 6

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

podarum
Quartz | Level 8

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
Fluorite | Level 6

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
Quartz | Level 8

Yes, I have it.

SAS/ACCESS  Interface to PC Files

LarryWorley
Fluorite | Level 6

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

Correct on all your questions. 

View Columns.png

LarryWorley
Fluorite | Level 6

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 33 replies
  • 2448 views
  • 0 likes
  • 8 in conversation