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

[emaguin]

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

[ghosh]

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

[emaguin]

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

[PaigeMiller]

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.

[ghosh]

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

[PaigeMiller]

---

@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.

[emaguin]

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. 

[PaigeMiller]

---

@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;

[emaguin]

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;

[Shmuel]

You should correcy the rename statement:

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

[Kurt_Bremser]

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;

[Shmuel]

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;

[ballardw]

---

@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.

[emaguin]

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?

[ghosh]

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;