character to numeric format; using if statement

[DonaZ]

Hi

Sometimes the variables that I import from Excel are character format and should be numeric format.

Rather than run
PROC CONTENTS DATA=test1
Each time

I used the following:

If year=2000 then do;

if var1=’’ then var1=’.’
Var1_num=input (var1,4.);
Drop var1;
Rename var1_num=var1;
Label var1_num=input (var1,4.);

if var2=’’ then var2=’.’
Var2_num=input(var2,4.);
Drop var2;
Rename var2_num=var2;
Label var2_num=input (var2,4.);

This works when the variables are text. The problem occurs if the variable(s) is numeric. If Var1 or Var2 is numeric, the content in the field becomes null.

Is there a condition statement that I could use to only change Var1 and Var2 to numeric if it is a character?

Thank you for your time.

DonaZ

 

[ChrisW75]

You could try something like this (the topic of how to change variable types has been discussed several times in previous threads):

data new;
  set old;

  var1_num = input(var1,4.);
  var2_char = put(var2,4char6.);
run;

Note your code above doesn't actually change the data type of the field you are working on.

Also, you can programmatically discover which fields need to be updated by using dictionary.columns...

proc sql;
  create table cols as
  select name
        ,type
        ,length
  from dictionary.columns
  where memname='WORK'
    and libname='OLD'
    ;
quit;

You can write this data out to macro variables and build up a fairly complex program to loop through the variables that need to be changed using macro code.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[DonaZ]

Chris,

I am not understanding this. My file is the libaries/work/imported.

How do I use this code?

proc sql;
create table cols as
select name
,type
,length
from dictionary.columns
where memname='WORK'
and libname='OLD'
;
quit;

Thank you for your time.

 

[ChrisW75]

Sorry, I made a mistake in the code.

proc sql;
  create table cols as
  select name
        ,type
        ,length
  from dictionary.columns
  where memname='OLD'
    and libname='WORK'
    ;
quit;

If you look at the results of this step, you'll see a list of all the variables in your table, with their type. You can then adjust the step to return only the fields you want to change.
From there you can build code to use this list to build further code to perform the translations that you want to make.
For instance:-

proc sql;
  select name
  into :NUMS separated by ' '
  from dictionary.columns
  where memname='CLASS'
    and libname='SASHELP'
    and type = 'num'
    ;
quit;
%put &NUMS;

You can use a %do loop and %SCAN to loop through these variables to build code up.
Here's a quick example I knocked up using the SASHELP.CLASS dataset. Submit this and look at the results and see if you understand how it works. If you do, it'll be pretty straigthforward to change it for your purposes.

proc sql noprint;
  select name
  into :NUMS separated by ' '
  from dictionary.columns
  where memname='CLASS'
    and libname='SASHELP'
    and type = 'num'
    ;

  select count(*)
  into :CNT
  from dictionary.columns
  where memname='CLASS'
    and libname='SASHELP'
    and type = 'num'
    ;
quit;
%put &NUMS;
%put &CNT;

%macro loop;
  %do i = 1 %to &CNT;
    %let VAR = %SCAN(&NUMS,&i);
    length &VAR._txt $6;
    &VAR._txt = put(&VAR,6.);
  %end;
%mend;

data test;
  set sashelp.class;

  %loop;
run;

If you have trouble following what it's doing, submit this

options mprint mlogic symbolgen;

Then re-run the code and you'll see the macro logic laid out for you.
Enjoy.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.