ERROR: All variables in array list must be the same type

[palolix]

Dear SAS Community,

I get this 'ERROR: All variables in array list must be the same type' when using this code after transposing data from wide to long format. All this variables (FruitWt, Color, Rind, Button, Wtloss) are numeric variables, but since my data set starts with missing values (.) maybe that is why? I would greatly appreciate your help!

data long;

    set new;

    array _FruitWt (*) Day0_FruitWt Day3_FruitWt Day7_FruitWt ;

    array _Color(*) Day0_Color Day3_Color Day7_Color ;

array _Rind (*) Day0_Rind Day3_Rind Day7_Rind;

array _Button (*) Day0_Button Day3_Button Day7_Button;

array _Wtloss (*) Day0_Wtloss Day3_Wtloss Day7_Wtloss;

    do i=1 to dim(_FruitWt);

        label=scan(vname(_FruitWt(i)), 1, "_");

        FruitWt=_FruitWt(i);

        Color=_Color(i);

Rind=_Rind(i);

Button=_Button(i);

Wtloss=_Wtloss(i);

        output;

    end;

    drop Day0_: Day3_: Day7_: i;

run;

Here is the data new:

Obs	Test	Grower	Treatment	FruitNr	Day0_FruitWt	Day0_Color	Day0_Rind	Day0_Button	TRT_day3	Day3_FruitWt	Day3_Color	Day3_Rind	Day3_Button	Day7_FruitWt	Day7_Color	Day7_Rind	Day7_Button	Day3_Wtloss	Day7_Wtloss
1	1	1	Initial	1	146.15	6	0	0	NA	.	.	.	.	.	.	.	.	.	.
2	1	1	Initial	2	218.42	5	0	3	NA	.	.	.	.	.	.	.	.	.	.
3	1	1	Initial	3	164.77	5	0	3	NA	.	.	.	.	.	.	.	.	.	.
4	1	1	Initial	4	174.28	6	0	3	NA	.	.	.	.	.	.	.	.	.	.
5	1	1	Initial	5	157.36	6	0	3	NA	.	.	.	.	.	.	.	.	.	.

[Ksharp]

That is easy. Using the following code to check whether all these variables (under ARRAY statement) are  numeric type or not ?

proc contents data=new varnum;
run;

[palolix]

Thank you Ksharp!

This is what I got:

Variables in Creation Order
#	Variable	Type	Len	Format	Informat	Label
1	Test	Num	8	BEST.		Test
2	Grower	Num	8	BEST.		Grower
3	Treatment	Num	8	TREATMENT.		Treatment
4	FruitNr	Num	8	BEST.		FruitNr
5	Day0_FruitWt	Num	8	BEST.		Day0_FruitWt
6	Day0_Color	Num	8	BEST.		Day0_Color
7	Day0_Rind	Num	8	BEST.		Day0_Rind
8	Day0_Button	Num	8	BEST.		Day0_Button
9	TRT_day3	Char	3	$3.	$3.	TRT_day3
10	Day3_FruitWt	Char	6	$6.	$6.	Day3_FruitWt
11	Day3_Color	Char	1	$1.	$1.	Day3_Color
12	Day3_Rind	Char	1	$1.	$1.	Day3_Rind
13	Day3_Button	Char	1	$1.	$1.	Day3_Button
14	Day7_FruitWt	Char	6	$6.	$6.	Day7_FruitWt
15	Day7_Color	Char	1	$1.	$1.	Day7_Color
16	Day7_Rind	Char	1	$1.	$1.	Day7_Rind
17	Day7_Button	Char	1	$1.	$1.	Day7_Button
18	Day3_Wtloss	Num	8
19	Day7_Wtloss	Num	8

[Ksharp]

As you showed 'Day3_FruitWt' ,'Day3_Color' ..... are all character type ,not numeric type.
Change these variables into numeric type by INPUT() .

[Tom]

How did you create the dataset NEW?  You mentioned that you transposed it.  How did you do that?  And how did you create the dataset you transposed to make NEW?

You mentioned missing values.  Whether a variable has any missing values should not change the TYPE of the variable.

But the TABLE (as in a table in a report, as opposed to an actual SAS dataset) that you shared does have the string 'NA' showing as the only values in the column headed by TRT_day3.

So perhaps you started with a CSV file generated by R or some other system that inserts that string instead of just leaving the field empty when the value is missing.  If you try to use some type of automatic tool for converting a CSV file into a SAS dataset (such as PROC IMPORT) then those NA strings in the CSV file will cause the variables to be made as character.  PROC IMPORT will also make any column that only has digit strings as the values as numeric, even if you meant the variable to be character. Also PROC IMPORT will define any variable that is missing on all observations as character (because a one byte character variable takes less storage space than an eight byte numeric variable).

[palolix]

Thank you so much Tom for your feedbak!

Actually I imported the data from excel like this:

proc import out=wide

    datafile="G:\Shared drives\Arpaia Lab Group\Citrus Degreening Project\Citrus degreening statistics\Degreening.xlsx"

dbms=xlsx

    replace;

sheet='Navel_all';

    getnames=YES;

run;

/*define format*/

proc format;

value Treatment   0 = 'Initial' 1 = 'Air' 2 = 'Air + C2H4' 3 = 'Air + 1% CO2' 4 = 'Air + 2.5% CO2' 5 = 'Air + 5% CO2';

/*apply format to data*/

data wide2;

set wide;

format Treatment Treatment.;

run;

/*create a new variable*/

data new;

set wide2;

Day3_Wtloss = 100 -(Day3_FruitWt/Day0_FruitWt *100);

Day7_Wtloss = 100 -(Day7_FruitWt/Day0_FruitWt *100);

/*view dataset*/

proc print data=new;

run;

In my excel file, only the variable TRT_day3 has character values, all the other have numeric values. However, variables Day3_FruitWt to Day7_Button start with missing values indicated by a (.)

Test	Grower	Treatment	FruitNr	Day0_FruitWt	Day0_Color	Day0_Rind	Day0_Button	TRT_day3	Day3_FruitWt	Day3_Color	Day3_Rind	Day3_Button	Day7_FruitWt	Day7_Color	Day7_Rind	Day7_Button
1	1	0	1	146.15	6	0	0	NA	.	.	.	.	.	.	.	.
1	1	0	2	218.42	5	0	3	NA	.	.	.	.	.	.	.	.
1	1	0	3	164.77	5	0	3	NA	.	.	.	.	.	.	.	.
1	1	0	4	174.28	6	0	3	NA	.	.	.	.	.	.	.	.
1	1	0	5	157.36	6	0	3	NA	.	.	.	.	.	.	.	.

[Tom]

Too bad the source is an XLSX file and not a simple text file, like a CSV.

That is because you do not have much control over how the variables are created when using PROC IMPORT (or the XLSX libname engine) when reading from an XLSX file.

In an spreadsheet, like your XLSX file, each cell is independent.  But when turning a column of the spreadsheet into a variable in the SAS dataset has to make all of the cells of that column into the same type of value (numeric or character).  Since you can represent a number as a character string easily, but not the reverse, then any column that has a mix of numeric and character cells must be made as character.

If the cell in the XLSX file literally has a period in it then it is TEXT and not a number.

Perhaps you just need to convert all of those periods into empty cells instead.   Do the same for cells that have the text NA (when that text is used to mean "not available").  Then reimport the spreadsheet and now the variables created from those columns should be numeric.

[palolix]

Thank you Tom! I think I will then try to convert to csv before importing.

[PaigeMiller]

All this variables (FruitWt, Color, Rind, Button, Wtloss) are numeric variables

It sounds like you are just stating this as a fact, rather than confirming this by running PROC CONTENTS. I have a rule here, when SAS says something is false and the human user says it is true, I believe SAS. (In my experience, SAS is right 99.99% of the time) So please check PROC CONTENTS.

[Kurt_Bremser]

When SAS tells you that the variable types don't match, then they do not match, period..

Run PROC CONTENTS to see where the issue is..

[ballardw]

I am going to throw in a style choice that your or your office is made that is making you write more code than is needed for array processing. 

By placing the interval measures of 0, 3, 7 (or others) in the middle of the variable name you keep yourself from using the variable list features of SAS.

If the variables were named for example FruitWt_Day0, FruitWt_Day3 and Fruitwt_Day7 you could write the ARRAY statement as:

Array _FruitWt (*) Fruitwt:  ;

The colon immediately after FruitWt tells SAS to use all the variables whose names start with Fruitwt. Or if you have other such variables then use Fruitwt_Day:  to get only those suffixed with Day.

Have you actually run Proc Contents yet? Or opened the data set and examined ALL the variables used in those array statements? Or actually closely read the LOG? That particular error will only appear immediately after the statement causing the error. Se this example from my log.

187  data junk;
188    set sashelp.class;
189    Array x (*) Name sex height;
ERROR: All variables in array list must be the same type, i.e., all
       numeric or character.
190    array y (*) age height weight;
191  run;

So be reading the log I can tell that one of the variables referenced on the Array X statement has a problem. So it is one of Name Sex or Height. The first two are character and the third is numeric.

As shown by this from Proc Contents:

Alphabetic List of Variables and Attributes
#	Variable	Type	Len
3	Age	Num	8
4	Height	Num	8
1	Name	Char	8
2	Sex	Char	1
5	Weight	Num	8

[palolix]

Thank you so much for your reply! This is the log I get:

35 data long;
36 set new;
37 array _FruitWt (*) Day0_FruitWt Day3_FruitWt Day7_FruitWt ;
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
38 array _Color(*) Day0_Color Day3_Color Day7_Color ;
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
39 array _Rind (*) Day0_Rind Day3_Rind Day7_Rind;
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
40 array _Button (*) Day0_Button Day3_Button Day7_Button;
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
41 array _Wtloss (*) Day0_Wtloss Day3_Wtloss Day7_Wtloss;
42 do i=1 to dim(_FruitWt);
43 label=scan(vname(_FruitWt(i)), 1, "_");
44 FruitWt=_FruitWt(i);
45 Color=_Color(i);
46 Rind=_Rind(i);
47 Button=_Button(i);
48 Wtloss=_Wtloss(i);
49 output;
50 end;
51 drop Day0_: Day3_: Day7_: i;
52 run;

[Tom]

Let's look at the first one.

37 array _FruitWt (*) Day0_FruitWt Day3_FruitWt Day7_FruitWt ;
ERROR: All variables in array list must be the same type, i.e., all numeric or character.

And looking at those three variables you can see that one is numeric and the other two are character.

5	Day0_FruitWt	Num	8	BEST.	 	Day0_FruitWt
10	Day3_FruitWt	Char	6	$6.	$6.	Day3_FruitWt
14	Day7_FruitWt	Char	6	$6.	$6.	Day7_FruitWt

[Tom]

One way you can try to deal with mixed type variables is to use the VVALUEX() function.  That will allow you to pass a string with the NAME of a variable and get back a string with the VALUE of the variable.  You can then use the INPUT() function to convert that string into a number.

So something like this will convert the data in to name/value pairs dataset that is actually LONG.

data actually_long;
  row+1;
  set new;
  length day 8 name $32 value 8 ;
  do day=0,3,7;
    do name='FruitWt','Color','Rind','Button','Wtloss';
      value = input(vvaluex(cats('Day',day,'_',name)),32.);
      output;
    end;
  end;
  drop Day0_: Day3_: Day7_: ;
run;

Then if you want you could use PROC TRANSPOSE to make your sort of long dataset.

proc transpose data=actually_long out=sort_of_long;
  by row day;
  id name;
  var value;
run;

Note: You can add the ?? modifier to the informat used in the INPUT() function call to suppress the notes in the log about invalid values such "NA" that might have been what caused the variables to be made as character.

value = input(vvaluex(cats('Day',day,'_',name)),??32.);

[palolix]

Thank you so much for your great support Tom! I noticed that the cells with numbers on them were in different format in excel so I changed them all to the same format and then I imported the data from a csv file to sas and that solved the problem!