topic Re: converting character variable to numeric AND preserving its place in variable sequence in SAS Programming

converting character variable to numeric AND preserving its place in variable sequence

emaguin — Wed, 21 Aug 2019 13:25:33 GMT

Suppose a file with variables x1 to x20 and except for x11, which is character, all variables are numeric and x1-x20 are from a single measure or scale (context is a research study using published scales).

I know i can convert x11 to numeric using input function as in x11a=input(x11,best32). However, doing so puts x11a, as a new variable, at the end of the variable order. It's very useful and desirable to preserve the original variable name and the original variable order. I suspect i can reorder the dataset by using a keep command (a function?, something else?) but i haven't tried this. That's the background.

This is what i want to know, first, does sas have a commmand, a whatever, that simultaneously converts a variable from character to numeric and retains the original name and its place in the variable sequence? (Some of you know i'm a lifelong spss user and for those of you that use spss--just occasionally--what i'm looking for is the sas equivalent to the spss alter type command (google it, its very nice).

Failing that which i suspect will be the case, is there a more efficient statement of this:

data outfile; set infile;

x11a=input(x11,best32);

drop x11;

x11=x11a;

keep studyid--x10 x11 x12--z59;

Thanks, Gene Maguin

Re: converting character variable to numeric AND preserving its place in variable sequence

ghosh — Wed, 21 Aug 2019 13:39:04 GMT

data x;
length x1-x10 5 x11 x11a $5 x12-x20 5 ;
run;
proc contents; run;

Re: converting character variable to numeric AND preserving its place in variable sequence

PaigeMiller — Wed, 21 Aug 2019 13:41:23 GMT

@emaguin wrote:

This is what i want to know, first, does sas have a commmand, a whatever, that simultaneously converts a variable from character to numeric and retains the original name and its place in the variable sequence?

No.

You could rename the old variable to be x11char and then rename x11a to x11.

I'm not sure why order in the data set is important, as any type of display or output allows you to specify the order you want.

Re: converting character variable to numeric AND preserving its place in variable sequence

emaguin — Wed, 21 Aug 2019 13:52:27 GMT

Thanks for your reply. My understanding of Length is that is returns the number of bytes used for the variable. If i'm misunderstanding your post, please correct me but i need x11 converted to numeric and retain the same name and same place in the order sequence. I don't care about how many bytes long it is.

Gene Maguin

Re: converting character variable to numeric AND preserving its place in variable sequence

PaigeMiller — Wed, 21 Aug 2019 13:55:01 GMT

The LENGTH statement (not the LENGTH function) can re-order the variables. It does not do the conversion of a character variable to a numeric variable.

Re: converting character variable to numeric AND preserving its place in variable sequence

ghosh — Wed, 21 Aug 2019 14:03:57 GMT

you are thinking of the length function.

You could define a numeric variable (say 11n) in the length statement as a placeholder which will hold the converted value of x11. in a subsequent data step drop x11 and rename x11n to x11.

Just an idea, have not tested it

Re: converting character variable to numeric AND preserving its place in variable sequence

emaguin — Wed, 21 Aug 2019 14:09:17 GMT

Thank you for your reply. I thought that was probably true but i wanted to check. Ok. just to be clear, you're saying i can do this?

data outfile; set infile;

x11a=input(x11,best32);

x11=x11a;

keep studyid--x10 x11 x12--z59;

Why preserve order? I know that proc freq can present tabs and crosstabs in different orders, which is very nice. Several reasons. We'll eventually compute scale scores as in, xmeasure1=mean (of x1-x6); xmeasure2=mean (of x7-x12); etc. (of course we can list each item but why if order is preseved.) We presume investigators using multiple different stats programs will use these data, not all of which are as flexible as sas. Preserving variable order enforces a structure that links back to the documentation.

Re: converting character variable to numeric AND preserving its place in variable sequence

PaigeMiller — Wed, 21 Aug 2019 14:20:01 GMT

@emaguin wrote:

Why preserve order? I know that proc freq can present tabs and crosstabs in different orders, which is very nice. Several reasons. We'll eventually compute scale scores as in, xmeasure1=mean (of x1-x6); xmeasure2=mean (of x7-x12); etc. (of course we can list each item but why if order is preseved.) We presume investigators using multiple different stats programs will use these data, not all of which are as flexible as sas. Preserving variable order enforces a structure that links back to the documentation.

It doesn't matter where in the SAS data set x11 is located. The language sees x1-x20 as valid, even if the variables are not in that order.

I would code it like this:

data outfile; 
    set infile(rename=(x11=x11char));
    x11=input(x11char,best32.);
    keep studyid x1-x20;
run;

Re: converting character variable to numeric AND preserving its place in variable sequence

Kurt_Bremser — Wed, 21 Aug 2019 14:20:44 GMT

You can use a retain statement to keep the order:

data class;
retain name sex age height weight;
set sashelp.class (rename=(sex=_sex));
if _sex = 'M' then sex = 1; else sex = 2;
drop _sex;
run;

Depending on the number of variables, you might want to create the variable list automatically:

proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname = "SASHELP" and memname = "CLASS";
quit;

data class;
retain &varlist.;
set sashelp.class (rename=(sex=_sex));
if _sex = 'M' then sex = 1; else sex = 2;
drop _sex;
run;

Re: converting character variable to numeric AND preserving its place in variable sequence

Shmuel — Wed, 21 Aug 2019 14:29:27 GMT

You can do it in one step:

data outfile;
     format x1-x20;  /* do not define format here. Just to keep order in outfile */
 set infile(rename=(x11=x11a));
     x11 = input(x11a, best32.);
     drop x11a;
run;

Re: converting character variable to numeric AND preserving its place in variable sequence

ballardw — Wed, 21 Aug 2019 14:48:24 GMT

@emaguin wrote:

Suppose a file with variables x1 to x20 and except for x11, which is character, all variables are numeric and x1-x20 are from a single measure or scale (context is a research study using published scales).

I know i can convert x11 to numeric using input function as in x11a=input(x11,best32). However, doing so puts x11a, as a new variable, at the end of the variable order. It's very useful and desirable to preserve the original variable name and the original variable order. I suspect i can reorder the dataset by using a keep command (a function?, something else?) but i haven't tried this. That's the background.

This is what i want to know, first, does sas have a commmand, a whatever, that simultaneously converts a variable from character to numeric and retains the original name and its place in the variable sequence? (Some of you know i'm a lifelong spss user and for those of you that use spss--just occasionally--what i'm looking for is the sas equivalent to the spss alter type command (google it, its very nice).

Failing that which i suspect will be the case, is there a more efficient statement of this:

data outfile; set infile;

x11a=input(x11,best32);

drop x11;

x11=x11a;

keep studyid--x10 x11 x12--z59;

Thanks, Gene Maguin

You comment implies that X11 should have been numeric all along. Why isn't it? You should have been able to read it from the source as numeric and avoid this whole issue.

Hint: An ounce of reading the data correctly at the start saves a lot of time writing "fix" code later.

Re: converting character variable to numeric AND preserving its place in variable sequence

emaguin — Wed, 21 Aug 2019 19:05:04 GMT

Yes, I think I understand what you are saying. Ok, the file is a 200 variable csv export from qualtricx, a survey program like surveymonkey. Excel is not involved. Record 1 is var names, record 2 is item text, which i've manually deleted. As you would judge correctly, I used proc import to read it. I think the alternative you are suggesting is something like this

proc file1; infile='file name string' dsd;

input v1 v2 $ v3 v4 v5 v6 $ a1-a5 b1-b20 c1-c34;

One question on what i wrote above. Let v3 and v4 be known numeric and v5 and v6 be known character. How do i distinguish v3 and v4 from v5 and v5 with respect to variable type?

You have a lot of experience. What are the tradeoffs between proc import and input? How would you choose one over the other?

Re: converting character variable to numeric AND preserving its place in variable sequence

ghosh — Wed, 21 Aug 2019 19:12:51 GMT

put in a length statement before input, as in:

data file1;
infile='file name string' dsd;
length v1 v2 v3 v4 5 v5 v6 $5 a1-a5 b1-b20 c1-c34 5;
input v1 v2 $ v3 v4 v5 v6 $ a1-a5 b1-b20 c1-c34;
run;

Re: converting character variable to numeric AND preserving its place in variable sequence

emaguin — Wed, 21 Aug 2019 19:56:29 GMT

This seems like a compact workable solution but in the real data there's problems i don't understand.

This is the real story for this one dataset, a little 200 variable dataset with nine distinct measures having 4 to 40 items per measure. These few need to be run through the vartype conversion process. You all are the experts but from the error pointer located at frirr7, i'd guess that sas won't accept a rename list. This is also just step one of the described sequence.

data W1Friday; set burst1.Burst1_Week1_Friday_S1 (rename=
(V3=V3x)(fr1rr7=fr1rr7x)(fr1rr21=fr1rr21x)(fr1rr26=fr1rr26x)
(fr1rr27=fr1rr27x)(fr1rr28=fr1rr28x)(fr1rr29=fr1rr29x)(fr1rr30=fr1rr30x)
(fr1rr31=fr1rr31x)(fr1ass6=fr1ass6x)(fr1ass7=fr1ass7x)(fr1ass8=fr1ass8x)
(fr1ass9=fr1ass9x)(fr1ass10=fr1ass10x)(fr1ass11=fr1ass11x)
(fr1ass12=fr1ass12x)(fr1ass14=fr1ass14x)(fr1ass15=fr1ass15x)
(fr1ass16=fr1ass16x)(fr1oa14=fr1oa14x));
run;

Re: converting character variable to numeric AND preserving its place in variable sequence

ballardw — Wed, 21 Aug 2019 21:32:38 GMT

@emaguin wrote:

Yes, I think I understand what you are saying. Ok, the file is a 200 variable csv export from qualtricx, a survey program like surveymonkey. Excel is not involved. Record 1 is var names, record 2 is item text, which i've manually deleted. As you would judge correctly, I used proc import to read it. I think the alternative you are suggesting is something like this

proc file1; infile='file name string' dsd;

input v1 v2 $ v3 v4 v5 v6 $ a1-a5 b1-b20 c1-c34;

One question on what i wrote above. Let v3 and v4 be known numeric and v5 and v6 be known character. How do i distinguish v3 and v4 from v5 and v5 with respect to variable type?

You have a lot of experience. What are the tradeoffs between proc import and input? How would you choose one over the other?

I actually use a lot of Proc Import to create a basic input program to customize, especially when given the stuff SurveyMonkey and similar will produce.

You had a good idea about reducing the header rows to one row before using proc import.

One thing that helps is to set the GUESSINGROWS option in proc import very high so more than 20 rows of data are examined before setting the variable types.

The second step is that when importing a CSV file SAS will write the generated data step code to the log. You can copy that code back into the editor, save and modify it. Most of the Format statements can be done away with unless you have preferences.

There will be an informat statement for each variable. You can examine that to see if the variable is of the expected type: $ indicates character, most numeric values will be Best32. (overkill but works). If one of the informats looks incorrect, change it. This may also be a time to use the editor search and replace to rename variables.

I will typically also had Label statements, likely using that information from the second row of the header if it was like the question text or similar.

The tradeoff, between the two procs, at least with the data sources I use is that the import will often create wrong variable types for frequently missing values (skip patterns in surveys or uncommon conditions in other data) vs the time to write data step code. For delimited files the latter is usually not much of problem using the proc import to generate the skeleton unless I have/want to rename lots of variables with names like Client_characteristic_first_visit_for__ (something that gets truncated because the actual column header exceeded 32 characters). I have had data files with literally 100+ column headers that all started with the exact same 20+ characters. Which means shortening them is well worthwhile. And why I add labels.

With many of my recurring data sources I actually create custom informats for variables to indicate such things as errors for variables that should not be missing, or validate against an expected list of known values (Race should not be Purple for instance) or to read some text into numeric values such as "Yes" or "No" to 1/0 codes.

Re: converting character variable to numeric AND preserving its place in variable sequence

Shmuel — Wed, 21 Aug 2019 22:29:19 GMT

You should correcy the rename statement:

data W1Friday; 
  set burst1.Burst1_Week1_Friday_S1 (rename=
   (V3=V3x  fr1rr7=fr1rr7x   fr1rr21=fr1rr21x   fr1rr26=fr1rr26x .....))

Re: converting character variable to numeric AND preserving its place in variable sequence

hashman — Wed, 21 Aug 2019 23:21:46 GMT

@emaguin:

KEEP has no effect of ordering PDV variables, so it won't work for the purpose. The most reliable method is to use an unvalued RETAIN as the first statement in the DATA step. This is because it makes the compiler defer the determination of the data types for the variables listed under the RETAIN till the end of the step during the compilation. (In fact, I'd even say that this is the only fully reliable method.)

Surely you'd need to prepare the RETAIN list beforehand, but it's easy to extract from the dictionary tables:

data infile ;                                   
  array aa ID x1-x10 (11*1) ;                   
  x11 = "11" ;                                  
  array zz x12-x20 (9*2) ;                      
run ;                                           
                                                
proc sql noprint ;                              
  select name into :invars separated by " "     
  from   dictionary.columns                     
  where  libname = "WORK" and memname = "INFILE"
  order varnum                                  
  ;                                             
quit ;                                          
                                                
data outfile (drop = ___) ;                     
  retain &invars ;                              
  set infile (rename = x11 = ___) ;             
  x11 = input (___, 32.) ;                      
run ;

Kind regards

Paul D.

Re: converting character variable to numeric AND preserving its place in variable sequence

Kurt_Bremser — Thu, 22 Aug 2019 07:05:05 GMT

@emaguin wrote:

You have a lot of experience. What are the tradeoffs between proc import and input? How would you choose one over the other?

As soon as a process needs to be repeatable, proc import is out of the question, period. See Maxims 22, 31 and 45.

Re: converting character variable to numeric AND preserving its place in variable sequence

emaguin — Thu, 22 Aug 2019 14:42:52 GMT

I want to try to do a wrap-up of this topic. First, I presented a simple (trivial) made up example when the real problem was more involved, which was my mistake. Many people responded and repeatedly. Thank you.

I'll use this code structure, which several suggested. So i think this part of the problem is solved.

data W1Friday_2; set W1Friday_1 (rename=
(V3=V3x fr1rr7=fr1rr7x fr1rr21=fr1rr21x));
V3=input(V3x,best32.);
fr1rr7=input(fr1rr7x,best32.);
fr1rr21x=input(fr1rr21,best32.);
drop V3x fr1rr7x fr1rr21x;
run;

With respect to the part about preserving variable order, several suggested either Length or Retain.

I've read the documentation about Retain and its seems to be about keeping values in memory across cases (and this is the use that Cody shows in his book). So, if i used Retain where would it be placed in the little example code shown above? Would the statement be this (this now the exact variable order):

Retain v1 v2 v3 v4-v10 Q_TotalDuration Q256 fr1alc1--fr1rr6 fr1rr7 f1fr1rr8-fr1rr20 fr1rr21 fr1rr22--end_1;

or this

Retain v1--end_1;

or this (which someone mentioned)

Retain

Now Length, where would it be placed?

And, how would the statement read?

Again, thank you.

Gene Maguin

Re: converting character variable to numeric AND preserving its place in variable sequence

PaigeMiller — Thu, 22 Aug 2019 14:52:52 GMT

RETAIN does have the use that you mention: "its seems to be about keeping values in memory across cases" — but it also forces the ordering of variables, which seems to be what you want. It can go anywhere in your DATA step, but it usually goes as the first command under the DATA command.

LENGTH can also force the ordering of the variables, it must go before the SET or MERGE or UPDATE statement in the DATA step.

You don't need both RETAIN and LENGTH to force the ordering of variables, one will do; although for other programming reasons you may need both.

Maybe I haven't been reading carefully, but it's still not clear to me why you need to put effort into re-ordering the variables. In your example, any code you write subsequently that uses the structure

v1-v10

will work properly, even if you don't re-order the variables.