topic Re: Create an array from a macro variable in SAS Programming

Create an array from a macro variable

klasko — Tue, 05 Jun 2018 18:34:53 GMT

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!

Re: Create an array from a macro variable

Dougall — Tue, 05 Jun 2018 20:16:58 GMT

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.

Re: Create an array from a macro variable

Astounding — Tue, 05 Jun 2018 20:40:58 GMT

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;

Re: Create an array from a macro variable

klasko — Wed, 06 Jun 2018 04:01:16 GMT

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?

Re: Create an array from a macro variable

Astounding — Wed, 06 Jun 2018 17:10:20 GMT

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.

Re: Create an array from a macro variable

klasko — Wed, 13 Jun 2018 20:00:14 GMT

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.

Re: Create an array from a macro variable

Astounding — Wed, 13 Jun 2018 20:46:58 GMT

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

Re: Create an array from a macro variable

Morsecode90 — Wed, 22 Jun 2022 08:44:24 GMT

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?

Re: Create an array from a macro variable

Astounding — Wed, 22 Jun 2022 19:11:44 GMT

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.

Re: Create an array from a macro variable

Tom — Wed, 22 Jun 2022 19:19:21 GMT

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

Re: Create an array from a macro variable

Tom — Wed, 22 Jun 2022 19:47:27 GMT

Do not convert NUMBERS (values of the CER variable) into STRINGS (value in the macro variable(s)) just to later try to convert them back into NUMBERS.

To convert multiple observations into multiple variables you can use PROC TRANSPOSE. For example if you sorted the data by ID and DATE you might want to generate one observation per ID instead of one observation per ID and DATE by using a PROC TRANSPOSE like this. The PREFIX= option says to name the generated variables as TASAS1, TASAS2 , etc.

proc transpose data=desa.cer  out=want(drop=_name_) prefix=TASAS ;
  by id ;
  var cer ;
run;

But the PHOTOGRAPH you posted of your data does not look like it is properly organized for transposing. Why the the observations all of different values of ID and DATE?

Also it looks like you are trying to subset data to just specific days in the year. Which year? Or are there multiple years? Does each ID have all of the years?

Here is simplified way to select those two days of the year (independent of which year) by using formats to convert the date values into strings you can test. The PUT() function with the DDMMYY10 format converts dates into strings that look alot like the values in your posted photograph, only they will always use two digits for day and month. The colon modifier on the equality test will match the string just to the length of the shorter of the two. So just the DD/MM part of the result of the PUT() function.

where put(fecha,DDMMYY10.) in: ('22/01' '22/07') ;

Perhaps you don't need to define any ARRAYs at all for what you are trying to do. If you create a series of variable that all have the same base name and numeric suffix with sequential values, like TASAS1, TASAS2 , ... then you can just reference them use a variable list where you need them in your code.

Remember that an ARRAY in SAS code is just a way to make it easy to reference many different variables by using an index into a list of the variable names. Those references are only valid in the data step where the ARRAY is defined. It is just a tool to make it so you can repeat similar statements without having to type "wallpaper" code.

Re: Create an array from a macro variable

Morsecode90 — Fri, 24 Jun 2022 15:33:51 GMT

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;

Re: Create an array from a macro variable

Tom — Fri, 24 Jun 2022 15:41:44 GMT

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.

Re: Create an array from a macro variable

Morsecode90 — Fri, 24 Jun 2022 16:25:09 GMT

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.

Re: Create an array from a macro variable

Tom — Fri, 24 Jun 2022 16:45:38 GMT

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;

Re: Create an array from a macro variable

Morsecode90 — Tue, 28 Jun 2022 09:57:58 GMT

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.