I have a dataset with a 1000 variables that are in character format. The values could be numbers or text. Does anyone know a macro that detect each field one by one and see if it’s a number converts it to numeric, if not leave it as character?
If possible, you need to examine the process that creates this data set and fix the problem there. If this is a conversion of an Excel file to a SAS data set, you probably can fix the problem by converting the Excel file again with the proper options, or by converting Excel to CSV, in which case then you can force certain columns to be numeric.
SAS has a tool for guessing what format text is using. PROC IMPORT.
Convert your data to a CSV file and then use PROC IMPORT to read it. Now compare the types between the two versions of the data and decide which one you want to use.
Just make sure not to use a tool for generating the CSV file that adds quotes around all character values (whether or not quotes are needed) as PROC IMPORT will assume that means the values should be read a character.
Below a way how you could implement what @Tom suggests.
data have(drop=_:);
array var {10} $10;
do _k=1 to 100;
do _i=1 to dim(var);
var[_i]=put(_n_,z9.);
if ceil(ranuni(1)*1000)>990 then var[_i]=cats('A',var[_i]);
end;
output;
end;
run;
filename extfile temp;
proc export
data=have
file=extfile
dbms=csv
replace
;
run;
proc import
out=want
file=extfile
dbms=csv
replace
;
guessingrows=max;
run;
filename extfile clear;
title 'Variable Types Before and After';
proc sql;
select libname, memname, name, type
from dictionary.columns
where
libname='WORK' and
memname in ('HAVE','WANT')
order by memname, varnum
;
quit;
title;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: