an easy way to convert numeric variable to character variable

[HG]

Hi community,

I need to import a lot of txt files. The variable ID in sas datasets can be either character or numeric.  When I set the sas datasets together I have to change the numeric ID to character ID. What is the efficient way to do it?

Thanks!

[SASJedi]

Use explicit conversion with a PUT or INPUT function.  For example:

/* Data set ONE has numeric ID */
data one;
   do id=1 to 3;
        T='One';
          output;
     end;
run;

/* Data set TWO has character ID */
data two;
   do id='4','5','6';
        T='Two';
          output;
     end;
run;

/* Concatenate ONE and TWO, make all ID's numeric */ 
Data ALL;
  set one
      two (rename=(ID=xID));
  IF not missing(xID) then ID=INPUT(xID,5.);
  drop x:;
run;

Results:

       id  T

    --------------

        1  One

        2  One

        3  One

        4  Two

        5  Two

        6  Two

[Haikuo]

Hi,

My suggestion kicks in before where you import txt files. 'ID' variable should always be character variable, as no matter what they look like, you can't do arithmetic operation on them anyway. Therefore, always import them as they are character variables.

Haikuo

[HG]

Hi SASjedi and Haikuo,

Thank you for your reply! I use proc import and call execute to input the txt files. How should I tell sas to import the IDs as character variables?

Thank you!

[Haikuo]

Hi HG,

You can't tell proc import what variable type , it decides on its own. Call execute is used to execute a macro, I can't tell you what it does without viewing your code.

use  'filename + data step + infile + input' to have a better control on your importing process.

Haikuo

[Patrick]

Agree with others that using a data step with infile/input gives you the control you need.

One way of reducing the typing for such a data step is to use EG's import wizard with one of your text files. That creates you the full data step code. Then copy the generated code and amend it to your needs so that it works with all your text files with the same structure.

[Hen]

I like Patrick's answer. Import data manually using File Import.... and then copy the code from the log. Paste it into Sas Editor and change from charater to numeric or vice-versa. eg

I imported a file where postcode was numeric and I wanted it to be character. Whereever postcode appeared in the code copied from the log I changed it to $4.

Data xx ;

paste the code from log

change bits you want (per expalnation above) ;

Run;

[shivas]

Hi Hen,

If you are using Base SAS then no need to paste the code from log,you can try importing file first and then just press F4 and you  can see all the code in the sas editor.

Thanks,

Shiva

[Linlin · Accepted solution]

Hi,

I saved two files x1 and x2 at c:\temp\forum.

  x1

id age

1 20

2 30

  x2

id age

a 20

b 30

c 40

below is my code:

%let location=c:\temp\forum\;

filename mydate pipe "dir &location\*.txt/b";

data _null_;

  infile mydate truncover;

  length fname _fname $30;

   input fname;

  _fname=cats('_',scan(fname,1));

  if upcase(first(fname))='X' then

  call execute("proc import out=work."||_fname||

                "  datafile="||"'&location."||fname||"'

   DBMS=TAB REPLACE;

     GETNAMES=YES;

  delimiter='09'x;

     DATAROW=2;run;

  data "||_fname|| ";length nid $ 30; set "||_fname||";nid=compress(put(id,20.));drop id;run;");

run;

  data want (rename=(nid=id));

   set _: indsname=ll;

   dsn=ll;

   run;

Linlin