DATA Step, Macro, Functions and more

an easy way to convert numeric variable to character variable

Accepted Solution Solved
Reply
Contributor HG
Contributor
Posts: 23
Accepted Solution

an easy way to convert numeric variable to character variable

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!


Accepted Solutions
Solution
‎06-02-2012 09:06 PM
Super Contributor
Posts: 1,636

Re: an easy way to convert numeric variable to character variable

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

View solution in original post


All Replies
SAS Employee
Posts: 104

Re: an easy way to convert numeric variable to character variable

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

Respected Advisor
Posts: 3,124

Re: an easy way to convert numeric variable to character variable

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

Contributor HG
Contributor
Posts: 23

Re: an easy way to convert numeric variable to character variable

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!

Respected Advisor
Posts: 3,124

Re: an easy way to convert numeric variable to character variable

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

Respected Advisor
Posts: 3,892

Re: an easy way to convert numeric variable to character variable

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
N/A
Posts: 1

Re: an easy way to convert numeric variable to character variable

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;

Super Contributor
Posts: 349

Re: an easy way to convert numeric variable to character variable

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

Solution
‎06-02-2012 09:06 PM
Super Contributor
Posts: 1,636

Re: an easy way to convert numeric variable to character variable

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 1351 views
  • 0 likes
  • 7 in conversation