BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HG
Calcite | Level 5 HG
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

8 REPLIES 8
SASJedi
SAS Super FREQ

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

Check out my Jedi SAS Tricks for SAS Users
Haikuo
Onyx | Level 15

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
Calcite | Level 5 HG
Calcite | Level 5

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
Onyx | Level 15

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

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
Calcite | Level 5 Hen
Calcite | Level 5

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
Pyrite | Level 9

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
Lapis Lazuli | Level 10

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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