Hi,
I have a series of data sets of equal size, each one of which has over 100 columns containing numbers. Due to data downloads into Excel, each data set often differs from the others in the data type of the column. For example, column 2 of data set 1 is numeric but column 2 of data set 2 is character. This poses a problem to me because later on I will need to stack up all these data sets vertically.
I wrote the following code to convert any columns which are of character types to numeric, so that all data sets will have numeric that will be joined vertically. I don't know why the code doesn't work. Errors occur, and if not, then character columns remain of character types and no conversion at all.
*Convert any column of char types to numeric;
data sheet1_1;
set sheet1 (drop=ISIN);
array vars{*}$ _CHARACTER_; *** work on all those columns in the data set that are of character type and ignore those which already are numeric;
do i=1 to dim(vars) by 1;
vars{i}=input(vars{i},BEST12.); *** convert from char to num;
end;
run;
Can you please help? Thanks a lot
You have to create NEW variables. One way is to use transpose and do a FLIP/FLOP.
data test;
id + 1;
input (x1-x10)(:f8. :$f8.);
cards;
1 2 3 4 5 6 7 8 9 10
3 49 98 17 87 18 18 18 77 77
;;;;
run;
proc sql noprint;
select name into :nums separated by ' '
from dictionary.columns
where libname eq 'WORK' and memname eq 'TEST'
and upcase(name) ne 'ID'
and upcase(type) eqt 'N';
quit;
run;
%put NOTE: NUMS=&nums;
proc transpose data=test out=chars;
by id;
var _char_;
copy &nums;
run;
data chars;
set chars;
num = input(col1,f16.);
run;
proc transpose data=chars out=allnums(where=(not missing(_name_)));
by id;
var num;
copy &nums;
run;
proc print;
run;
Bookmarked!
Here is another approach. Use the proc sql call to create the renames and new calculations. e.g.:
data sheet1;
input isin $ a b $ c d $ e f $;
cards;
x 1 2 3 4 5 6
;
proc sql noprint;
select trim(name)||"=input(n_"||trim(name)||",BEST12.);",
trim(name)||"=n_"||trim(name)
into :makenum separated by " ",
:renames separated by " "
from dictionary.columns
where libname="WORK" and
memname="SHEET1" and
type="char" and
name ne "isin"
;
quit;
data sheet1_1 (drop=n_:);
set sheet1 (drop=ISIN rename=(&renames.));
&makenum;
run;
Hi Art,
I have included the numeric variables in the final dataset:
data have;
aa=66;
bb=88;
cc=99;
input (a b c d e)($) ;
cards;
1 2 3 4 5
;
proc sql noprint;
select catx(' ','input(',name,',best12.) as',name) into : list1 separated by ','
from dictionary.columns
where libname='WORK' and memname='HAVE' and type ='char';
select name into : list2 separated by ','
from dictionary.columns
where libname='WORK' and memname='HAVE' and type ne 'char';
create table want as
select &list1,&list2 from have;
quit;
proc print data=want;run;
proc contents data=want;run;
Linlin
Message was edited by: Linlin
Linlin: Since the OP indicated having both numeric and character variables in the file you would have to expand on that to account for the numeric variables.
Hi Art,
Thank you. I just focused on “convert char to num for all _CHARACTER_”.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.