Recoding character variables into numeric

[Angi]

Hi,

I have some character variables in my dataset named var1_1, var1_2, var1_3, var2_1, var2_2 etc. The text in each column starts with the numerical suffix of the variable name but the text that follows is different.

For example:

var1_1 = 1. This is apple.

var1_1 = 1. That is a car.

Var1_2 = 2. I like to read.

Var 1_2= 2. I am happy.

And so on.

I want to recode these character variables into numeric variables so that

re_var1_1=1 if there is any text in this column.

re_var1_2=2 if there is any text in this column.

re_var1_3=3 if there is any text in this column.

re_var2_1=1 if there is any text in this column.

and so on.

Any suggestions on how to get this done?

Thank you,

Angi

proc sql;
create table want(year float, community varchar(1), var1_1 varchar(157), var1_2 varchar(290), var1_3 varchar(318), var2_1 varchar(82), var2_2 varchar(46), var2_3 varchar(78));
Insert into want(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'A', '1. It is a truth universally acknowledged, that a single man in possession of a good fortune, must be in want of a wife.', '', '', '', '', '3. Every summer Lin Kong returned to Goose Village to divorce his wife, Shuyu.');
Insert into want(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'B', '', '2. Happy families are all alike; every unhappy family is unhappy in its own way.', '', '1. Through the fence, between the curling flower spaces, I could see them hitting.', '', '');
Insert into want(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'C', '', '', '3. You are about to begin reading Italo Calvino''s new novel, If on a winter''s night a traveler.', '', '2. I am a sick man . . . I am a spiteful man.', '');
Insert into want(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'D', '', '', '3. If you really want to hear about it, the first thing you''ll probably want to know is where I was born, and what my lousy childhood was like, and how my parents were occupied and all before they had me, and all that David Copperfield kind of crap, but I don''t feel like going into it, if you want to know the truth.', '', '', '3. Mrs. Dalloway said she would buy the flowers herself.');
Insert into want(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'B', '1. Many years later, as he faced the firing squad, Colonel Aureliano Buendía was to remember that distant afternoon when his father took him to discover ice.', '', '', '', '', '');
Insert into want(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'E', '', '2. It was the best of times, it was the worst of times, it was the age of wisdom, it was the age of foolishness, it was the epoch of belief, it was the epoch of incredulity, it was the season of Light, it was the season of Darkness, it was the spring of hope, it was the winter of despair.', '', '', '', '');
Insert into want(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'M', '1. You don''t know about me without you have read a book by the name of The Adventures of Tom Sawyer; but that ain''t no matter.', '', '', '1. The moment one learns English, complications set in.', '', '');

proc print data=want;
run;

[ed_sas_member · Accepted solution]

Hi @Angi 

I am thinking about something like this to automatize the process :

proc sql;
	create table have(year float, community varchar(1), var1_1 varchar(157), var1_2 varchar(290), var1_3 varchar(318), var2_1 varchar(82), var2_2 varchar(46), var2_3 varchar(78));
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'A', '1. It is a truth universally acknowledged, that a single man in possession of a good fortune, must be in want of a wife.', '', '', '', '', '3. Every summer Lin Kong returned to Goose Village to divorce his wife, Shuyu.');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'B', '', '2. Happy families are all alike; every unhappy family is unhappy in its own way.', '', '1. Through the fence, between the curling flower spaces, I could see them hitting.', '', '');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'C', '', '', '3. You are about to begin reading Italo Calvino''s new novel, If on a winter''s night a traveler.', '', '2. I am a sick man . . . I am a spiteful man.', '');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'D', '', '', '3. If you really want to hear about it, the first thing you''ll probably want to know is where I was born, and what my lousy childhood was like, and how my parents were occupied and all before they had me, and all that David Copperfield kind of crap, but I don''t feel like going into it, if you want to know the truth.', '', '', '3. Mrs. Dalloway said she would buy the flowers herself.');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'B', '1. Many years later, as he faced the firing squad, Colonel Aureliano Buendía was to remember that distant afternoon when his father took him to discover ice.', '', '', '', '', '');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'E', '', '2. It was the best of times, it was the worst of times, it was the age of wisdom, it was the age of foolishness, it was the epoch of belief, it was the epoch of incredulity, it was the season of Light, it was the season of Darkness, it was the spring of hope, it was the winter of despair.', '', '', '', '');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'M', '1. You don''t know about me without you have read a book by the name of The Adventures of Tom Sawyer; but that ain''t no matter.', '', '', '1. The moment one learns English, complications set in.', '', '');
quit;

proc sql noprint;
	/* Retrieve the number of variables starting by 'var'*/
	select count(name)
	into: nb_var
	from dictionary.columns
	where libname="WORK" and memname="HAVE" and upcase(name) like 'VAR%';
	/* Retrieve the list of variables starting by 'var'*/
	select name
	into: char_var separated by " "
	from dictionary.columns
	where libname="WORK" and memname="HAVE" and upcase(name) like 'VAR%';
	/* Retrieve the list of new variables to be created*/
	select catx('_','re',name)
	into: char_num separated by " "
	from dictionary.columns
	where libname="WORK" and memname="HAVE" and upcase(name) like 'VAR%';
quit;

data want;
	set have;
	array _vchar(&nb_var.) $ &char_var.;
	array _vnum(&nb_var.) &char_num.;
	do i=1 to dim(_vchar);
		_vnum(i) = input(substr(_vchar(i),1,1),best.);
	end;
	drop i;
run;

Best,

[ed_sas_member]

Hi @Angi 

Here is another approach, using PROC TRANSPOSE:

proc sql;
	create table have(year float, community varchar(1), var1_1 varchar(157), var1_2 varchar(290), var1_3 varchar(318), var2_1 varchar(82), var2_2 varchar(46), var2_3 varchar(78));
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'A', '1. It is a truth universally acknowledged, that a single man in possession of a good fortune, must be in want of a wife.', '', '', '', '', '3. Every summer Lin Kong returned to Goose Village to divorce his wife, Shuyu.');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'B', '', '2. Happy families are all alike; every unhappy family is unhappy in its own way.', '', '1. Through the fence, between the curling flower spaces, I could see them hitting.', '', '');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'C', '', '', '3. You are about to begin reading Italo Calvino''s new novel, If on a winter''s night a traveler.', '', '2. I am a sick man . . . I am a spiteful man.', '');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'D', '', '', '3. If you really want to hear about it, the first thing you''ll probably want to know is where I was born, and what my lousy childhood was like, and how my parents were occupied and all before they had me, and all that David Copperfield kind of crap, but I don''t feel like going into it, if you want to know the truth.', '', '', '3. Mrs. Dalloway said she would buy the flowers herself.');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'B', '1. Many years later, as he faced the firing squad, Colonel Aureliano Buendía was to remember that distant afternoon when his father took him to discover ice.', '', '', '', '', '');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'E', '', '2. It was the best of times, it was the worst of times, it was the age of wisdom, it was the age of foolishness, it was the epoch of belief, it was the epoch of incredulity, it was the season of Light, it was the season of Darkness, it was the spring of hope, it was the winter of despair.', '', '', '', '');
	Insert into have(year, community, var1_1, var1_2, var1_3, var2_1, var2_2, var2_3) Values(2014, 'M', '1. You don''t know about me without you have read a book by the name of The Adventures of Tom Sawyer; but that ain''t no matter.', '', '', '1. The moment one learns English, complications set in.', '', '');
quit;


proc transpose data=have out=have_tr;
	var var:;
	by year community notsorted;
run;

data have_tr2;
	set have_tr;
	_newname_ = catx('_','re',_name_);
	_newcol_ = input(substr(col1,1,1),best.);
run;

proc transpose data=have_tr2 out=want (drop=_:);
	var _newcol_;
	id _newname_;
	by year community notsorted;
run;

Best,

[Angi]

Thank you for your help @ed_sas_member !

[Patrick]

Here a way using SAS data step array processing.

data have;
  infile datalines dsd dlm='|' truncover;
  input (var1_1 var1_2) (:$40.);
  datalines;
1. This is apple.|2. I like to read.
1. That is a car.|2. I am happy.
1. That is a car.|
;

%let cvars=;
%let nvars=;
proc sql noprint;
  select 
    name, 
    cats('re_',name) 
      into 
        :cvars separated by ' ', 
        :nvars separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='HAVE' 
      and upcase(name) like 'VAR%' 
      and type='char'
  ;
quit;
%put &=cvars;
%put &=nvars;

data want(drop=_:);
  set have;
  array cvars {*} &cvars;
  array nvars {*} &nvars;

  do _i=1 to dim(cvars);
    if not missing(cvars(_i)) then 
      do;
        length _vname $32.;
        _vname=vname(cvars(_i));
        nvars(_i)=input(scan(_vname,-1,'_'),? best32.);
      end;
  end;
run;

proc print;
run;

[Angi]

Thank you for your help @Patrick 

[ballardw]

If this data starts out as something external to SAS it may be easier or appropriate to "fix" at the time the data is read.

If this does start as something external could you provide a few lines of the source file (assuming it is not a Spreadsheet or DBMS)?

And what the result for that should look like.

Paste text file into a box opened on the forum using the </> to preserve formatting. The message windows will reformat text.

[Angi]

Thank you for the suggestion @ballardw. The file was sent as a spreadsheet 😞