topic Re: Problem with creating a two-dimensional array in SAS Programming

Problem with creating a two-dimensional array

Amanda_Lemon — Sat, 01 Jul 2017 22:49:59 GMT

I am a beginning user and have a problem with creating a two-dimensional array. Any advice would be highly appreciated. Thank you in advance.

I have a data set with 6 variables (ID and var1-var5) and 10 people. The data set is in Excel. I want to create a two-dimensional array (people by var), so I need a 10x5 array.

I have no problem importing the data into SAS. It correctly displays the data table:

But when I try to ask for a 10x5 array, this is what I get (goes on to ex50):

Here is my syntax:

PROC IMPORT OUT = WORK.Example DATAFILE = "C:\Desktop\Example.xls"
DBMS = xls REPLACE;
SHEET = "Example";
GETNAMES = YES;
RUN;

proc print data = example;
run;

data example;
array ex{10,2:6};
run;

proc print;
run;

Here is the log:

1632 PROC IMPORT OUT = WORK.Example DATAFILE = "C:\Desktop\Example.xls"
1633 DBMS = xls REPLACE;
1634 SHEET = "Example";
1635 GETNAMES = YES;
1636 RUN;

NOTE: The import data set has 10 observations and 6 variables.
NOTE: WORK.EXAMPLE data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

1637 proc print data = example;
1638 run;

NOTE: There were 10 observations read from the data set WORK.EXAMPLE.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.07 seconds
cpu time 0.00 seconds

1639 data example;
1640 array ex{10,2:6};
1641 run;

NOTE: The data set WORK.EXAMPLE has 1 observations and 50 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

1642 proc print;
1643 run;

NOTE: There were 1 observations read from the data set WORK.EXAMPLE.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.07 seconds
cpu time 0.01 seconds

Re: Problem with creating a two-dimensional array

art297 — Sat, 01 Jul 2017 23:27:58 GMT

It's easy to turn that into an array, but why do you want to do that? What are you trying to accomplish?

Art, CEO, AnalystFinder.com

Re: Problem with creating a two-dimensional array

Amanda_Lemon — Sun, 02 Jul 2017 00:08:19 GMT

Thank you for your prompt response. I need to be able to work with the cells.

Specifically, I have a dataset of students' responses and corresponding teacher responses. The teacher responses, however, are not about a student but about a class. Some students were in the same class and thus have the same corresponding teacher responses. I want to identify class ID's by comparing teacher responses (if they are the same, the ID would be the same).

I have a good idea how to accomplish that and already drafted a syntax using arrays. The problem is -- I can't turn the dataset into an array... Once I do that, I will be good to go.

Thank you.

Re: Problem with creating a two-dimensional array

art297 — Sun, 02 Jul 2017 00:40:42 GMT

That's the way an array looks in SAS (other than with SAS IML).

One can still process it as a mutlti-dimensional array, but there is probably a much easier way to do what you want.

What defines teacher in your data and what do you want your resulting data to look like?

However, to answer your question, the first datastep below creates some test data, while the second loads that data into a 50x6 array. If you look at the resulting log (after you run the code), you'll see that all of the array elements can be seen and, if desired, processed. However, that is typically a very inefficient way to use SAS:

data have (drop=j);
  input var1-var5;
  array vars(5) var1-var5;
  do id=1 to 50;
    if mod(id,2) then do;
      do j=1 to 5;
        vars(j)=vars(j)-1;
      end;
      output;
      do j=1 to 5;
        vars(j)=vars(j)+1;
      end;
    end;
    else output;
  end;
  cards;
2 3 4 5 2 3
;

data want;
  set have end=eof;
  array vars_in(5) var1-var5;
  array vars(50,6) _temporary_;
  vars(_n_,1)=id;
  do i=1 to 5;
    vars(_n_,i+1)=vars_in(i);
  end;
  if eof then do;
    do i=1 to 50;
      put vars(i,1)= vars(i,2)= vars(i,3)= vars(i,4)= vars(i,5)=;
    end;
  end;
run;

Art, CEO, AnalystFinder.com

Re: Problem with creating a two-dimensional array

Cynthia_sas — Sun, 02 Jul 2017 01:34:01 GMT

Hi:
And for a good explanation of two-dimensional arrays and how the variables get created, please take at look at Example 4 starting on page 9 in this paper: https://support.sas.com/rnd/papers/sgf07/arrays1780.pdf

The relevant information is at the top of page 9, where it says: "You can think of a two-dimensional array as having 'rows' and 'columns', but to SAS, a two-dimensional array is no different than a one-dimensional array. It is still an alias for data set variables in the PDV or for temporary data elements."

It's a good paper and sheds light on how you work with multi-dimension arrays.

cynthia

Re: Problem with creating a two-dimensional array

Reeza — Sun, 02 Jul 2017 02:03:14 GMT

You're going about this incorrectly. You don't need to use arrays here. I would strongly suggest posting sample data for what you're trying to do and someone can show you how to arange your data to obtain the results. It's best to use SAS 'way' of doing this rather than trying to fit it into another languages concepts. The array method is easier to think about because that's how we're taught math but it's not applicable here.

To join by ID you would do a MERGE or JOIN on the two datasets and then you would be able to make your comparisons.

You probably can load your data into IML to work with it as a matrix but that's not how SAS processes data, it moves through the data line by line.

Re: Problem with creating a two-dimensional array

Amanda_Lemon — Sun, 02 Jul 2017 02:29:57 GMT

Hi all,

Thank you for your responses.

I feel that I am going in a wrong direction... Using the syntax of Art (thank you, Art!), I got this (goes on to 50):

And that's not exactly what I want -- I want a 10x5 array, not 10x50 -- I played around but couldn't get 10x5...

Reeza, I am attaching the sample data. So, I have 10 students. var1-var5 are teacher responses about the class a student was in. Some students were in the same classes. For students who were in the same classes, teacher responses are the same. Task: to identify class IDs (i.e., students in the same classes will have the same class IDs). I put the correct answer - the correct class IDs - in the sample data file attached, so that you know what result I am looking for.

How should I approach this?

P.S. I learned Pascal years ago and the array method worked well there. Now I am trying to apply Pascal logic to SAS... but apparently it's not working... Maybe I should use a different programming language in which I can use the array method? Will R be suitable for that?

Re: Problem with creating a two-dimensional array

art297 — Sun, 02 Jul 2017 02:48:39 GMT

You haven't said what you want to do with the data. Assigning class numbers is simple. I used the following code. My assigned numbers are different than yours, but follow the same pattern:

proc import datafile='/folders/myfolders/example.xls' dbms=xls out=have replace;
run;

proc sort data=have out=want;
  by var1-var5;
run;

data want;
  set want;
  by var1-var5;
  if first.var5 then class+1;
run;

But, I can't answer your question of what language would be better without knowing what you are trying to accomplish. I also used to program in Pascal (and Fortran, Basic and many other languages). My guess is that you will find SAS to be a lot more powerful than any of them.

Art, CEO, AnalystFinder.com

Re: Problem with creating a two-dimensional array

Reeza — Sun, 02 Jul 2017 02:50:26 GMT

At the end of the day, which language you use is entirely up to you. R is free so that's helpful for sure.

My issue with R is the many, many different packages and ways of doing things. If you're doing basic processing you shouldn't have too many issues in either languages. Both have many free resources online to teach you the languages.

For SAS you can start here:

support.sas.com/training/tutorial

It has a free e-course, see bottom right hand corner, for the link.

I honestly can't recall if I worked with Pascal back in the day or not...I've played around with too many languages 😞

data Response;
infile cards dlm='09'x;

input ID var1 var2 var3 var4 var5;
cards;
1	3	4	5	2	3
2	3	4	5	1	3
3	3	4	4	3	4
4	3	4	5	1	3
5	4	5	5	5	5
6	4	5	5	5	5
7	3	4	4	3	4
8	4	1	2	4	6
9	3	4	5	2	3
10	4	5	5	5	5
;
run;

*sort into order;
proc sort data=response;
by var1-var5;
run;

*Identify each new group of variables;
data want;
set response;

by var1 var2 var3 var4 var5; *specify how data is sorted;

if first.var5 then class+1; *identify unique groups;

run;

*print to display;
proc print data=want;
run;

Re: Problem with creating a two-dimensional array

Reeza — Sun, 02 Jul 2017 02:51:48 GMT

And sometimes you'll get answers that are the exact same seconds apart here.

I'm impressed, we even referenced the variables the same way @art297

Re: Problem with creating a two-dimensional array

Tom — Sun, 02 Jul 2017 13:57:45 GMT

You have some good answers on how to create groups of records that the share same set of values.

In terms of comparisons to other languages it is probably better to think of SAS in terms of relational databases. Think of a SAS dataset not as a matrix but as a series of observations of a fixed number of variables. In that case a two dimensional matrix is normally represented by three variables. One to store the actual value and one each for the row and column index.

I am still not sure what your data represents or even what calculation you are trying to do. It kind of looks like your first column is the student id, but I am not sure what your 5 variables represent. Are they the responses of 5 teachers?

data have ;
  input student @;
  do teacher=1 to 5 ;
    input rating @;
    output;
  end;
cards;
1	3	4	5	2	3
2	3	4	5	1	3
3	3	4	4	3	4
4	3	4	5	1	3
5	4	5	5	5	5
6	4	5	5	5	5
7	3	4	4	3	4
8	4	1	2	4	6
9	3	4	5	2	3
10	4	5	5	5	5
;

Or perhaps they represent the 5 classes that the student had and the values are the ids of the teacher of the class?

data have ;
  input student @;
  do class=1 to 5 ;
    input teacher @;
    output;
  end;
cards;
1	3	4	5	2	3
2	3	4	5	1	3
3	3	4	4	3	4
4	3	4	5	1	3
5	4	5	5	5	5
6	4	5	5	5	5
7	3	4	4	3	4
8	4	1	2	4	6
9	3	4	5	2	3
10	4	5	5	5	5
;

Re: Problem with creating a two-dimensional array

Amanda_Lemon — Wed, 05 Jul 2017 00:53:03 GMT

Thank you all! Dear Reeza and Art, when running your script, I get this error in the log:

81 data want;
82 set example;
83 by var1 var2 var3 var4 var5;
84 if first.var5 then class+1;
-
400
ERROR 400-185: The SUM statement requires numeric expression.

85 run;

I am sure there is a very easy fix to that but I can't figure out... Could you please help me with that? Thank you in advance.

Re: Problem with creating a two-dimensional array

Amanda_Lemon — Wed, 05 Jul 2017 00:57:31 GMT

As for my question about other languages, I guess to be more specific the question would be as follows: which programming language will allow me to convert a data set into a 2-dimensional array and then work easily with such an array?

Thank you!

Re: Problem with creating a two-dimensional array

Reeza — Wed, 05 Jul 2017 01:25:32 GMT

@Amanda_Lemon wrote:

Thank you all! Dear Reeza and Art, when running your script, I get this error in the log:

81 data want;
82 set example;
83 by var1 var2 var3 var4 var5;
84
if first.var5 then classCount + 1;
-
400
ERROR 400-185: The SUM statement requires numeric expression.

85 run;

I am sure there is a very easy fix to that but I can't figure out... Could you please help me with that? Thank you in advance.

I suspect you already have a variable called CLASS in your dataset which is a character variable. Pick any other variable name, that you don't have in your data set and change CLASS to that.

Re: Problem with creating a two-dimensional array

Reeza — Wed, 05 Jul 2017 01:29:29 GMT

@Amanda_Lemon wrote:

As for my question about other languages, I guess to be more specific the question would be as follows: which programming language will allow me to convert a data set into a 2-dimensional array and then work easily with such an array?

Thank you!

Within SAS, IML will allow you to do that. But if you have character variables you'll also run into issues, because matrices/arrays are typically numeric. R dataframe will allow you to store mixed types. Python and R are fairly similar when it comes to analysis.

Re: Problem with creating a two-dimensional array

Amanda_Lemon — Wed, 05 Jul 2017 02:03:14 GMT

WOW, it works now! Yes, that was the problem. Thank you!

Last quick question -- just to make sure I understand the syntax -- what does "if first.var5" exactly mean? What does SAS do when it sees this?

Thank you again!

Re: Problem with creating a two-dimensional array

Amanda_Lemon — Wed, 05 Jul 2017 02:07:11 GMT

I haven't thought about IML... Thank you, Reeza!

Just to make sure I understand how it works -- do you mind showing how to convert a data set into a 2-dimensional matrix using IML?

Thanks again!

Re: Problem with creating a two-dimensional array

Reeza — Wed, 05 Jul 2017 03:44:31 GMT

@Amanda_Lemon wrote:
WOW, it works now! Yes, that was the problem. Thank you!

Last quick question -- just to make sure I understand the syntax -- what does "if first.var5" exactly mean? What does SAS do when it sees this?

Thank you again!

Every time it encounters a new value of var5 that (first value of var5) in a group it increments the counter.

Re: Problem with creating a two-dimensional array

Amanda_Lemon — Wed, 05 Jul 2017 03:50:52 GMT

Wait, why is it var5 that has a new value? I thought it should be that if the whole set (var 1 through var 5) is new, then increment the counter... no?

Re: Problem with creating a two-dimensional array

Reeza — Wed, 05 Jul 2017 04:32:56 GMT

@Amanda_Lemon wrote:
Wait, why is it var5 that has a new value? I thought it should be that if the whole set (var 1 through var 5) is new, then increment the counter... no?

The BY has all 5 listed so it's grouped within those variables. Since var5 is the last it uniquely identifies your groups.

Ie If Var2 stays the same it may still be a new group.

BY group processing is incredibly powerful. It allows you to identify groups in your data for specific processing, so you can do the exact same thing for each BY group without loops. Run the following code as an example. You'll get a chart for each make.

Proc sort data=SASHELP.cars out=cars;
By make;
Run;

Proc SGPLOT data=cars;
By make;
Scatter x=mpg_city y=mpg_highway;
Run;