Create an array from a macro variable

[klasko]

Hi!

I've been struggling with a minor task. I want to extract numbers from a single column on an Excel and use it in a Risk Dimensions process, and I would like to have an array in which every item is an observation (one of the numbers that I extracted from the Excel file).

I would like to end up with something like: tasas1 = 95.354 tasas 2= 95.272 and so on (not exactly, as I only want the observations corresponding to the 22th of January of July, but that's the idea).

So, I got the following code:

%let path = "/data/user/XX";
libname desa "/data/user/XX";


data AA;
set desa.cer;

if ((month(fecha) = 07 or month(fecha)= 01) and  day(fecha) = 22) then
output;
run;

proc sql noprint;
select cer into :emp_list separated by ' ' from AA;
quit;

data BB;
array tasas [6];
do i = 1 to 6;
tasas[i] =  scan(&emp_list, i, ' ');
i = i+1;
end;
run;

I know the macro variable emp_list has all the values I want:

The issue comes when I try to assign each item in the array the value extracted from emp_list. I get: 

ERROR 388-185: Expecting an arithmetic operator.

ERROR 200-322: The symbol is not recognized and will be ignored.

And when I use other characters and separators (such as |) I get:

Is there any way to get it right?

Thanks!

[Dougall]

Is there any reason you need to use the macro variable?

Seems to me this would be an excellent use of a transpose statement.

For example:

After the AA data step, use

proc transpose data=AA out=BB prefix=tasas;

var CER;

run;

This has the added benefit of producing as many or as few columns as there are records in AA.

[Astounding]

Loading a list of values into an array is easier than you would think.  You have this code:

data BB;
array tasas [6];
do i = 1 to 6;
tasas[i] =  scan(&emp_list, i, ' ');
i = i+1;
end;
run;

Try this version:

data BB;
array tasas [6] (&emp_list);
run;

[klasko]

Thanks! It worked, but I came into another problem. It creates the variables tasas1 to tasas6, but when I try to use them in a DO loop I get ERROR: Undeclared array referenced: tasas.
ERROR: Variable tasas has not been declared as an array.

I'm kind of lost. Didn't I create an array named tasas?

[Astounding]

Array definitions are temporary.  Arrays exist only in the same DATA step that contains the ARRAY statement.  You might need to repeat the ARRAY statement in a later DATA step.

[klasko]

What do I have to do if I want to use those values in a DO Loop inside a proc compile?

I can't use macros.

[Astounding]

Sorry, I don't think I will be able to help with PROC COMPILE.

[Tom]

---

@klasko wrote:

What do I have to do if I want to use those values in a DO Loop inside a proc compile?

I can't use macros.

---

What is PROC COMPILE?

[Morsecode90]

does it works on multidimentional arrays too? 

let's suppose i've got 

data want;

array two_dim_array[&N_rows,&N_cols] &col1. &col2. .... &colN.;

run;

will it works or there are other metod to create them?

[Astounding]

It works for multidimensional arrays, but you have to be careful to supply the correct number of elements.  With those dimensions:

{&n_rows, &n_cols}

the proper number of elements is not N, but &n_rows * &n_cols.

[Morsecode90]

thanks a lot, 

just another question, do you have any suggestion on how to load a table into those arrays?

my table has 6 rows and 77 columns

there are 2 problems: 

-even if the columns are all char they have different length, someone is  more than 8char long;

-the macrovariable compile the array with 6*77=462 but they appear to be all cols instead of rows*cols.

  my code is looking like this

proc sql;
select *
into : DISNEY_CHAR separated by '" "'
from char_by_season;
quit;

data DOGS_IN_DISNEY;
array dogs[&n_rows.,&n_cols.] "&DISNEY_CHAR.";
/*where n_rows=6 and n_cols=77 */
do r=1 to 6;
	do c=1 to 77;
		if dogs[r,c] in("Goofy","Pluto") then output;
	end;
end;
run;

[Tom]

Where is the data coming from?  What structure does it have there?  What structure do you want to create?

Why do you think this has anything to do with ARRAYs?

A SAS dataset is already a MATRIX.  The observations are the ROWS.  The variables are the COLUMNS.

[Morsecode90]

hi, 

i want to load a table wich has (at the moment) 77 variables and 6 observations into a bidimentional array because i have to filter every single combination of the two dimensions in the table with a specific rule mapped into another table.

In order to do so, i have to read both the field and the rule and i have to do it dynamically because both tables may vary in both dimentions (nrows,ncols). the only way that i've been able to think to solve it is by using arrays. 

the input table has all char attributes but their lenght range from 1 to 200. 

the rules table has 5 variables,  1 is a key while the combination of the remainings gives out the rules.

variables of the data table are the keys in the rules table.

i have almost no experience in sas so any help would be appreciated.

thanks for the help.

[Tom]

What I normally do in that case is use the RULE metadata to generate CODE.  Then run the CODE on the actual DATA.

The most complex one I have seen is for LAB normal range checks.  To the RULE metadata has the labcode and the logic for calculating status.  So the code to generate the code would essentially read the RULE metadata and write the code lines to a file.  So you might generate something like this into a temporary file:

  select (labcode);
     when ('ABCD') do;
        high = value > 2.0*ULN ;
        low = .z < value < 2.0*LLN;
       end;
    when ('XYZ') do;
        high = value > 125 ;
        low = .z < value < 50 ;
        end;
  end;

Then use code like this to run it against the actual lab data.

data want;
  set have;
%include code ;
run;

[Morsecode90]

thanks again, that's the way i'm using the metadata's sheets when i need to create a table, but in this case i'm using them with a different purpose. 

the data table is the result of a merge between a dinamic number of tables (it can be 2 or 1000).

what i want to do is to check if ther's any duplicates by key, if duplicates are presents then in the metadata i have described the weight by attributes coming from each source table. the one with heavier weight overwrite the others wich need to be saved into a dump table for future tracking, the issue here is that the weight are listed by attribute and not by source table.