topic Can a dataset with c variables and n rows be created from an n x c array? in SAS Enterprise Guide

Can a dataset with c variables and n rows be created from an n x c array?

RobertWF1 — Tue, 28 Dec 2021 23:24:37 GMT

I've unsuccessfully attempted to use nested do loops to output the array observations to an empty dataset with variables x1-x&cols.

Any suggestions?

%let numrows=8;
%let numcols=3;
data work.test_combs_1;
input x1-x&numcols;
datalines;
. . .
;
run;
data work.test_combs_2;
set work.test_combs_1;
  array test_array[&numrows,&numcols] (%eval(&numrows*&numcols)*0);
  do i=1 to &numrows;
    do j=1 to &numcols;
      x&j = (test_array[i,j]);
	  output;
	end;
  end;
run;

num

Re: Can a dataset with c variables and n rows be created from an n x c array?

Tom — Wed, 29 Dec 2021 01:46:25 GMT

You need another array.

  array test_array[&numrows,&numcols] (%eval(&numrows*&numcols)*0);
  array x [&numcols] ;
  do i=1 to &numrows;
    do j=1 to &numcols;
      x[j] = test_array[i,j];

Macro variables are not dataset variables. Also remember that the macro processor finishes modifying the code BEFORE the SAS compiler tries to interpret it. And so definitely BEFORE that data step can run. To see what code you are trying to run replace the macro variable references with example values.

But what are you trying to do? You are replicating every observation read from TEST_COMB_1 by a factoer of &num_rows times &num_cols. And you are setting all of the X variables to zeros because that is all you put into the array. Unless there are variables named TEST_ARRAY1,2,..... that you are reading from TEST_COMB_1. In which case why bother to set initial values when you define the array?

Re: Can a dataset with c variables and n rows be created from an n x c array?

RobertWF1 — Wed, 29 Dec 2021 03:24:29 GMT

Thanks Tom!
Really all I need to do is write the observations in an r x c array into a dataset having the same dimensions, with r rows and c variables x1, x2, ...

Re: Can a dataset with c variables and n rows be created from an n x c array?

Tom — Wed, 29 Dec 2021 03:49:42 GMT

You need to move the OUTPUT statement outside of the inner DO loop.

Move the values from the variables referenced by the multi-dimensional array to the variables referenced by the single dimension array and THEN write the values to the output dataset.

An ARRAY is a data step construct to allow you to reference one of a series of variables via an index.

How are you getting data into the ARRAY that you defined? Perhaps there is more to your program than you are showing?

So ignoring the issue of how you are going to get the data into your multi-dimensional array (AKA a matrix) here is how you could write that data out to a dataset.

So if you have 6 observations and 5 variables (AKA 6 rows and 5 columns) the code would look something like this:

data want;
   array matrix [6,5] ;
   array vars [5] age ht wt sysbp diabp;
* do something to populate the MATRIX array ;
* where is the data coming from? ;
* Write the matrix array to a dataset ;
  do obs=1 to 6;
    do col=1 to 5;
      vars[col] = matrix[obs,col];
    end;
    output;
  end;
run;

Re: Can a dataset with c variables and n rows be created from an n x c array?

andreas_lds — Wed, 29 Dec 2021 06:38:01 GMT

@RobertWF1 wrote:

I've unsuccessfully attempted to use nested do loops to output the array observations to an empty dataset with variables x1-x&cols.

Why? The usage of arrays in sas differs in many ways from their usage in other programming languages: it is just a shortcut to access multiple variables defined in one observation, all having the same type. It seems as if you are trying to transpose a dataset, or am i on the wrong track?

Re: Can a dataset with c variables and n rows be created from an n x c array?

RobertWF1 — Wed, 29 Dec 2021 15:32:40 GMT

I'm working on a project at work where I have to calculate the expected value of an average treatment effect (ATE) for an outcome (like total medical cost) for a simulated dataset using a data generating process ( a regression).

This involves calculating the expected value across all combinations of k dummy variables. SAS doesn't have a procedure or function that performs this, but Rick Wicklin posted a how-to article (https://blogs.sas.com/content/iml/2011/01/05/creating-a-matrix-with-all-combinations-of-zeros-and-ones.html) which discusses using the binary format option to recast numbers as their binary equivalent, the end result being an output matrix containing all 2^k combinations of 1s and 0s.

However my employer hasn't purchased the IML module so I've had to use *arrays* rather than matrices (and plenty help here in the SAS forum!).

I can generate the array of 1s and 0s now, but in order to find the expected ATE I have to convert the array into a dataset.

Re: Can a dataset with c variables and n rows be created from an n x c array?

RobertWF1 — Wed, 29 Dec 2021 17:11:03 GMT

Ah, I just read the comments at the bottom of Rick Wicklin's post - and see that someone posted a simple macro for creating a dataset with all combinations of 1s and 0s, using a Cartesian product in proc sql. Very handy & simple to use, although not quite sure I understand what his code is doing here:

proc sql;
select memname into:d separated by ','
from dictionary.tables
where upcase(libname)="WORK" and upcase(substr(memname,1,3))="__D"
;
quit;

Re: Can a dataset with c variables and n rows be created from an n x c array?

Tom — Wed, 29 Dec 2021 17:33:44 GMT

@RobertWF1 wrote:
Ah, I just read the comments at the bottom of Rick Wicklin's post - and see that someone posted a simple macro for creating a dataset with all combinations of 1s and 0s, using a Cartesian product in proc sql. Very handy & simple to use, although not quite sure I understand what his code is doing here:

proc sql;
select memname into:d separated by ','
from dictionary.tables
where upcase(libname)="WORK" and upcase(substr(memname,1,3))="__D"
;
quit;

That step is making a macro variable, &D, that contains a space delimited list of all of the work datasets whose name starts with two underscores and the letter D. Presumably to be used later in a SET or other statement where you could reference a list of datasets.

Re: Can a dataset with c variables and n rows be created from an n x c array?

Tom — Wed, 29 Dec 2021 17:37:33 GMT

A dataset is essentially a matrix with NOBS == NROWS and NVARS == NCOLS.

You can use a data step to multiply two such matrices (with a little work). I did that 25-30 years ago to deal with matrices my wife needed to multiply that were too large to load into memory to use IML (or PROC MATRIX back then).

You can also use PROC SCORE to multiply matrices.