Hello:
My previous dataset contain exclu1-exclu10, ract1-ract6, sym1-sym11. The exclu1, ract1-4, sym1-5 are numeric, and the rest are chartered. Therefore, I use input to change the Character to Numeric variable. I found out that new name has to assign before input statement. If I just use same variable name, it didn't work. Is this correct? In addition, anyone know how to simply my codes below. Thanks.
data Test_format;
set Test;
exclu2_ = input(exclu2, 2.);
exclu3_ = input(exclu3, 2.);
exclu4_ = input(exclu4, 2.);
exclu5_ = input(exclu5, 2.);
exclu6_ = input(exclu6, 2.);
exclu7_ = input(exclu7, 2.);
exclu8_ = input(exclu8, 2.);
exclu9_ = input(exclu9, 2.);
exclu10_ = input(exclu10, 2.);
race5_12_ = input(race5_12, 2.);
race6_12_ = input(race6_12, 2.);
sym6_ = input(sym6, 2.);
sym7_ = input(sym7, 2.);
sym8_ = input(sym8, 2.);
sym9_ = input(sym9, 2.);
sym10_ = input(sym10, 2.);
sym11_ = input(sym11, 2.);
run;
data Test_format2;
set Test_format;
drop exclu2
exclu3
exclu4
exclu5
exclu6
exclu7
exclu8
exclu9
exclu10
race5_12
race6_12
sym6
sym7
sym8
sym9
sym10
sym11;
rename exclu2_=exclu2
exclu3_=exclu3
exclu4_=exclu4
exclu5_=exclu5
exclu6_=exclu6
exclu7_=exclu7
exclu8_=exclu8
exclu9_=exclu9
exclu10_=exclu10
race5_12_=race5_12
race6_12_=race6_12
sym6_=sym6
sym7_=sym7
sym8_=sym8
sym9_=sym9
sym10_=sym10
sym11_=sym11;
run;
This will reduce the code a lot:
%macro myhelper;
data Test_format;
set Test;
%do i = 2 %to 10;
exclu&i._ = input(exclu&i., 2.);
drop exclu&i.;
rename exclu&i._ = exclu&i.;
%end;
race5_12_ = input(race5_12, 2.);
race6_12_ = input(race6_12, 2.);
drop race5_12 race6_12;
rename
race5_12_ = race5_12;
race6_12_ = race6_12;
;
%do i = 6 %to 11;
sym&i._ = input(sym&i., 2.);
drop sym&i.;
rename sym&i._ = sym&i.;
%end;
run;
%mend;
%myhelper
But all this has the smell of bad data structure that could be improved by transposing.
This will reduce the code a lot:
%macro myhelper;
data Test_format;
set Test;
%do i = 2 %to 10;
exclu&i._ = input(exclu&i., 2.);
drop exclu&i.;
rename exclu&i._ = exclu&i.;
%end;
race5_12_ = input(race5_12, 2.);
race6_12_ = input(race6_12, 2.);
drop race5_12 race6_12;
rename
race5_12_ = race5_12;
race6_12_ = race6_12;
;
%do i = 6 %to 11;
sym&i._ = input(sym&i., 2.);
drop sym&i.;
rename sym&i._ = sym&i.;
%end;
run;
%mend;
%myhelper
But all this has the smell of bad data structure that could be improved by transposing.
Once a variable is created the type, numeric or character is fixed. If you want to have the same name you can rename the previous version.
If the values are supposed to be numeric the best approach is to go back to when the values were originally created or read and do it properly.
The single most common cause I see on this board for what you describe is use of Proc Import which is a guessing procedure. It examines a few rows of data from a file and then guesses if a variable is numeric or character, and if character how long it should be.
Taking control of reading the data as needed will save you literally hundreds of hours "fixing" things if you continue to rely on Proc Import for reading data.
Worse is when the data is in a spreadsheet as there is no consistency of data cell contents within columns.
Best is to read data from a text file format such as CSV and have a data step to read with desired properties. You can get a basic data step from Proc Import and a delimited file, such as CSV by looking in the log after import, copy the data step code generated, paste into the editor and modify the code. With CSV or other delimited import use the GUESSINGROWS=<a large number> option so that more rows of the data are examined to set properties. You still want to check lengths of character variables and if numeric values are read as such and if dates or times are involved the proper date and time informats.
Ideally you should have documentation that describes your file to help fine-tune the above generated code. Or to write a data step from scratch (not that hard for delimited files).
This way you control what your data looks like.
Thanks for your suggestion, Ballardw
Thank you soooooooooooooooooooo much!
Maybe the easiest thing is just do a Flip-Flop-2-numeric
data Test_format;
retain id;
retain exclu1-exclu15 race4_12 race5_12 race6_12 race7_12 sym1-sym15;
retain exclu2-exclu9 race5_12 race6_12 sym6-sym11 '44';
retain _numeric_ 12;
attrib _all_ label='Variable label';
do id = 1 to 3;
output;
end;
run;
proc transpose data=Test_format out=flip;
by id;
var exclu1--sym15;
run;
data flip;
set flip;
num = input(left(col1),f16.);
run;
proc transpose data=flip out=allnum(drop=_name_);
by id;
var num;
run;
proc contents varnum;
ods select position;
run;
proc print;
run;
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 25. 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.