All observations dropped after converting variable type SAS

Question

I have two data sets that I'm merging (called "trends" and "insurance") and both have similar variables. One such similar variable is "Race", except in the "trends" dataset, Excel has coded it as a character variable, and in the "insurance" dataset, Excel coded it as numeric. Of course I can't merge these two variables if they aren't the same type, and I tried changing the type to numeric in Excel under the "Format" option. It didn't appear to work. Once I re-imported my XLS file in SAS, it continued to say "Race" was character, not numeric.

So I tried to do the conversion in SAS, like this:

DATA trends;
new = input(Race, 8.);
drop Race;
RENAME new = Race;
RUN;

Here's the output.

NOTE: Numeric values have been converted to character values at the places   
given by:
(Line):(Column).
66:13
NOTE: Variable Race is uninitialized.
NOTE: The data set WORK.TRENDS has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

But what I ended up with when I PROC CONTENTS DATA = trends; is NO observations at all in the new dataset. There should be over 800 observations and over 20 variables, just as there is when I originally import the XLS file into SAS.

Here is what the output of PROC CONTENTS looks like after converting "Race" to numeric:

Obs Race 
1   .

What happened to all my observations and other variables? Basically, the entire dataset "trends" is missing now.

In addition to the error I point out, you should know that there are probably better ways to handle this (via modifying your import statement and/or importing in a different way). Excel is not necessarily at fault here (as Excel mostly doesn't have "types"); it's more likely the input conversion. Feel free to ask a separate question about that, or simply browse for questions about setting type on import. — Joe

Joe Joe · Accepted Answer · 2017-03-01T16:13:34

DATA trends;
new = input(Race, 8.);
drop Race;
RENAME new = Race;
RUN;

Where is SAS getting the input data for race from? You need a set statement to get it from an already existing dataset, or input and infile to get it from a text file.

In this case it sounds like it should be:

DATA trends;
  set trends;
  new = input(Race, 8.);
  drop Race;
  RENAME new = Race;
RUN;

Though I always recommend a different name to the new dataset, as you can't rerun this step if you were to find some mistake. data trends_racenum; set trends;... would be good for example (to show it has the numeric race in it).

All observations dropped after converting variable type SAS

1 Answers