Input statement and macro?

[ybz12003]

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;

[Kurt_Bremser · Accepted solution]

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.

[ballardw]

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.

[ybz12003]

Thanks for your suggestion, Ballardw

[ybz12003]

Thank you soooooooooooooooooooo much!

[data_null__]

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;