converting numeric to character and vice versa

[Siddharth123]

Dear All,

I have a SAS dataset with 1000 variables and some of these are numeric and some characters. The file is saved as SASdata.Test. So, what I am trying to do is that I have a list of 400 variables which MUST be character and 600 MUST be numeric. However, when I import dataset 800 get assigned numeric values and 200 character.

Suppose I assign the 500 charcater variables as %let char= %str(a1 b1 c1.........).

Is it possible to write a macro which will check if the variable in SASdata.test has any of the variable in common with &char and which is numeric then convert it to $9. format?

This is what I think for handling character variables. But for numeric conversion, I do not have an idea.

Please could anyone help me.

Regards

sk

[ballardw]

I would start with how you import the data. What was the original data source? How did you bring the data into SAS?

If you know which variables should be numeric and character, which seems likely given the example you are thinking of, then make sure they import correctly at the beginning and you'll greatly simplify your data issues. Especially if you're going to get multiple data sets in the same format in the future.

You won't be able to change the data type without creating new variables.

Is there some specific reason you are trying to use macro code?

There are a number of ways to determine if variables are of a given type from looking at the variable in a datastep using functions VTYPE or VTYPEX to using Proc SQL and the dictionary table that describes data set contents Dictionary.columns.

A generic approach to creating a numeric variable from character data is to use the input function

numvar = input(charactervariablename, best12.); /* or other numeric informat if you know something about the data*/

And do you have dates in the data as well?

[SASKiwi]

Where are you importing your data from and why are your variables not being imported with the right type to begin with?

It is usually better to deal with this type of problem while importing rather than fixing it up afterwards.

[Haikuo]

The following code is only for number-Char conversion, but as you will figure out, it can be easily expanded to Char-Number. The idea is : first to get the variable names of what you want to convert, then you will need a codegen to do the job. It will have may variants, but main idea will be similar.

/*The purpose is to convert 'Length' and 'Weight' to Char*/

/*This is what you have in term of numeric var list*/

proc sql;

     create table var_num_cur as

           select distinct name from dictionary.columns where LIBNAME='SASHELP' AND MEMNAME='CARS' AND TYPE='num';

QUIT;

/*This is the wanted numeric variable list*/

data var_num_tobe;

     set var_num_cur;

     if name not in ('Length', 'Weight');

run;

/*Codegen*/

proc sql;

     /*construct rename part*/

     select cats(name,'=','_',name) into :ren separated by ' ' from var_num_cur

           where name not in (select name from var_num_tobe);

     /*construct drop part*/

     select cats('_',name) into  :_name separated by ' ' from var_num_cur

           where name not in (select name from var_num_tobe);

     /*construct the number to char conversion*/

     select catt(name,'=','put','(_',name,', 9.);') into :cv separated by ' ' from var_num_cur

           where name not in (select name from var_num_tobe);

quit;

data cars_new;

     set sashelp.cars (rename=(&ren.));

     &cv.

           drop &_name;

run;

Good luck,

Haikuo

[Tom]

So if you have 1000 defined variables you should store that information in a structured way, rather than as macro variables.  You could create a table that looks like the output of PROC CONTENTS with variables for NAME, TYPE, LENGTH, FORMAT and LABEL.  Or you could just create a representative table and use PROC CONTENTS on that table to generate the data set with the metadata.

Once you know the types that the variables should have then you can skip using PROC IMPORT and instead generate the code to import the file yourself.   If the issue is that each file might have only some of the 1000 possible variables and/or that the order of the variables changes then just read the first line of the file to find the names and merge it with your metadata and generate the code to read the rest of the file.  This could in fact be very simple.  Let's assume that your raw input file is called "mydata.csv" and that you have a sample file with all of the possible variables named SASDATA.TEMPLATE.  To use that to generate the code to read "mydata.csv" into SASDATA.TEST would be very simple since you just need to read the first line and convert it into INPUT and KEEP statements.

filename code temp;

data _null_;

  infile "myfile.csv" lrecl=300000 obs=1;

  input ;

  _infile_ = translate(_infile_,' ',',');

  file code ;

  put 'input ' _infile_ ';';

  put 'keep ' _infile_ ';' ;

run;

data sasdata.test ;

  if 0 then set sasdata.template;

  infile "myfile.csv" lrecl=300000 dsd truncover firstobs=2 ;

  %inc code / source2 ;

run;