Help with array macro (or possibly better way)?

[G_I_Jeff]

Need to remove growing iterative SAS code for graphing data by a single variable. I've started reading up on macros and have tried the following (as an example):

DATA TESTS;                                               

INPUT NAME $ SCORE1 SCORE2 SCORE3 SCORE4 SCORE5          

              SCORE6 SCORE7 SCORE8 SCORE9 SCORE10;        

CARDS;                                                   

ALICE 100 97.3 88.9 85.5 91.2 88.8 93.4 98.1 100 92      

CARRIE 88.7 81.4 92.8 92.1 93 93.7 92 81.9 85.5 84       

DARRYL 92.3 91 91 86.9 85 84.1 81.8 87.5 91 92.3         

FRANK 79.8 77 82 85.7 85.5 81.2 88 79.9 77 86.6          

HOLLY 83.4 85.1 88.7 92 81.3 84 81.9 87.3 80.6 79.6      

JENNIFER 85.3 88.6 87.1 82.9 89.5 88.7 92.8 95.8 93.3 98.2

JOHN 76.3 77.3 82.5 84 73.8 78.3 80.2 80.9 79.7 84.8     

MIKE 100 100 97.6 96.7 99.8 98 95.9 97.8 100 97.3        

SALLY 86.6 89 92.3 94.5 86.1 91 91.7 86.4 91 91.9        

TOM 86.4 88.2 81.9 84 85 82.6 91.7 88.2 91.7 84.8        

;                                                        

RUN;                                                      

PROC SUMMARY DATA=TESTS;                                  

  BY NAME;                                                

VAR SCORE1;                                              

OUTPUT OUT=STUDENTS                                      

SUM=;                                                    

%ARRAY(STUDENT,DATA=STUDENTS,VAR=NAME);                   

I keep getting "ERROR 180-322: STATEMENT IS NOT VALID OR IT IS USED OUT OF PROPER ORDER." on the %ARRAY line.

I tried simple %LET logic and it works fine assigning macro variables values.

I'm am new to macro logic in SAS and am a little stumped on this one.

Is there a better way I should be accomplishing this? Once I get an array, I want to loop through macro logic that produces several graphs for each variable.

Thanks for any help/suggestions!

Jeff

[AncaTilea]

Hi.

What are you trying to accomplish?

Sorry just finished reading the entire post.

Ok, so you could define a macro:....umm, but what kind of plot??

%macro plot(var = );

%do i = 1 %to 10;

proc gplot data = tests;

     plot &var&i. * &var&i.;

run;quit;

%end;

%mend plot;

%plot(var = score);

provide a bit more detail

[G_I_Jeff]

I'm trying to assign an array with the summarized student names so that I can use it through a loop.

[AncaTilea]

Is this what you want?

proc transpose data = tests out = foo;

    var score:;

    id name;

run;

[Tom]

Macro just generates SAS code.  So show the SAS code you want to generate and explain what parts need to vary.

[Astounding]

If you want to diagnose this yourself, here's an approach.  Look at these two options:  MPRINT, MFILE

Used in combination, they will let  you save the SAS code that your macro has generated in a separate file.  By then using that file as your program, the error messages will become much clearer.  All the macro language will be gone, and the error-related messages will be a lot easier to diagnose.  The most likely outcome is that  you will understand how your macro is generating the incorrect SAS code, and can tweak it to generate the correct code.

If you need help diagnosing, you will have to show your macro and possibly more (such as your log).  It's not possible to diagnose a vague SAS language error without seeing the SAS program.

Good luck.

[G_I_Jeff]

Astounding,

I don't have a program yet because I'm trying to design/write it and using the example I gave in my initial post is my attempt to do/show what I'm trying to accomplish. I can't even tell you if what I want to accomplish can be done. I'm reading articles about assigning macro arrays and trying it myself.

I basically want to loop through unique variable values from a DATA step, that's it.

The code I currently have is iterative logic that have hard coded values through multiple graphing statements to produce web reports and is growing. I don't want to have to add code every time the data changes.

I'm probably not explaining myself properly.

[G_I_Jeff]

Here is what I am trying to accomplish, through this example (and pardon my SAS code, I'm winging this):

DATA TESTS;                                              

INPUT NAME $ SCORE1 SCORE2 SCORE3 SCORE4 SCORE5         

              SCORE6 SCORE7 SCORE8 SCORE9 SCORE10;       

CARDS;                                                  

ALICE 100 97.3 88.9 85.5 91.2 88.8 93.4 98.1 100 92     

CARRIE 88.7 81.4 92.8 92.1 93 93.7 92 81.9 85.5 84      

DARRYL 92.3 91 91 86.9 85 84.1 81.8 87.5 91 92.3        

FRANK 79.8 77 82 85.7 85.5 81.2 88 79.9 77 86.6         

HOLLY 83.4 85.1 88.7 92 81.3 84 81.9 87.3 80.6 79.6     

JENNIFER 85.3 88.6 87.1 82.9 89.5 88.7 92.8 95.8 93.3 98.2

JOHN 76.3 77.3 82.5 84 73.8 78.3 80.2 80.9 79.7 84.8    

MIKE 100 100 97.6 96.7 99.8 98 95.9 97.8 100 97.3       

SALLY 86.6 89 92.3 94.5 86.1 91 91.7 86.4 91 91.9       

TOM 86.4 88.2 81.9 84 85 82.6 91.7 88.2 91.7 84.8       

;                                                       

RUN;                                                     

PROC SUMMARY DATA=TESTS;                                 

  BY NAME;                                               

VAR SCORE1;                                             

OUTPUT OUT=STUDENTS                                     

SUM=;                                                   

%ARRAY(STUDENT,DATA=STUDENTS,VAR=NAME);

%DO %STUDENT=1 to n (n being the max number of variables in macro array STUDENT);

     PROC SGPLOT DATA=HISTORY CYCLEATTRS;

        WHERE STUDENT=%STUDENT;

         TITLE ' Student's grade history';

         VBAR DATE / FILL RESPONSE=GRADES DATALABEL LEGENDLABEL='Students grades';

         VBAR DATE / FILL RESPONSE=AVERAGE DATALABEL LEGENDLABEL='District averages';

         XAXIS LABEL='Date';

         YAXIS LABEL='Grades';

     RUN;

    PROC TABULATE DATA=TESTS F=COMMA3.1;

       WHERE NAME=%STUDENT

        ......

    RUN;

%END

[Tom · Accepted solution]

If the number of students is small I recommend storing the list of names in a macro variable using a delimiter that cannot appear in any name value.

proc sql noprint ;

  select distinct name into :students separated by '|' from students;

%let nstudents=&sqlobs ;

quit;

%do i=1 %to &nstudents ;

  %let student = %scan(&students,&i,|);

  ....

%end;

[G_I_Jeff]

Tom,

That's a great example and your using SQL which I haven't attempted yet. I'll give it a shot!

Jeff

[AncaTilea]

How about this?

DATA TESTS;                                              

INPUT NAME $ SCORE1 SCORE2 SCORE3 SCORE4 SCORE5         

              SCORE6 SCORE7 SCORE8 SCORE9 SCORE10;       

CARDS;                                                  

ALICE 100 97.3 88.9 85.5 91.2 88.8 93.4 98.1 100 92     

CARRIE 88.7 81.4 92.8 92.1 93 93.7 92 81.9 85.5 84      

DARRYL 92.3 91 91 86.9 85 84.1 81.8 87.5 91 92.3        

FRANK 79.8 77 82 85.7 85.5 81.2 88 79.9 77 86.6         

HOLLY 83.4 85.1 88.7 92 81.3 84 81.9 87.3 80.6 79.6     

JENNIFER 85.3 88.6 87.1 82.9 89.5 88.7 92.8 95.8 93.3 98.2

JOHN 76.3 77.3 82.5 84 73.8 78.3 80.2 80.9 79.7 84.8    

MIKE 100 100 97.6 96.7 99.8 98 95.9 97.8 100 97.3       

SALLY 86.6 89 92.3 94.5 86.1 91 91.7 86.4 91 91.9       

TOM 86.4 88.2 81.9 84 85 82.6 91.7 88.2 91.7 84.8       

;

RUN;                                                     

PROC SUMMARY DATA=TESTS;                                 

  BY NAME;                                               

VAR SCORE1;                                             

OUTPUT OUT=STUDENTS                                     

SUM=; 

run;

proc transpose data = tests out = foo;

    var score:;

    id name;

run;

proc sql;

    select name into: names separated by " "

    from tests;

quit;

%let num_students = %eval(%sysfunc(countw(&names.)));

%macro ARRAY();

%DO i = 1 %to &num_students.;

     PROC SGPLOT DATA = foo;

         TITLE " Student's grade history";

         VBAR %scan(&names., &i., " ") / FILL RESPONSE= %scan(&names., &i., " ")   DATALABEL LEGENDLABEL='Students grades';

/*         VBAR DATE / FILL RESPONSE=AVERAGE DATALABEL LEGENDLABEL='District averages';*/

         XAXIS LABEL="Date";

         YAXIS LABEL="Grades";

     RUN;

/*    PROC TABULATE DATA=TESTS F=COMMA3.1;*/

/*        WHERE name = %scan("&names.", &i., " ");*/

/*        */

/*        ......*/

/**/

/*    RUN;*/

%END;

%mend array;

%array();

[Tom]

Your quotes are getting confused here.  In MACRO logic quotes do not really have any special meaning.  They are important in regular SAS statements as that is how SAS can tell whether the word ALICE should be interpreted as a literal string or a variable name or SAS syntax word. SAS does not care whether you use single quotes ('ALICE') or double quotes ("ALICE") to represent string literals.  But macro variable references and other macro statements will not resolve inside of single quotes.

So your WHERE statement that is testing if the dataset variable NAME has a particular value that is being generated by the macro logic would look like this:

WHERE name = "%scan(&names,&i,%str( ))" ;

Note that it is usually clearer and easier to maintain in this type of loop where a value is going to be used in multiple places to create a macro variable with the value once and then reference the new macro variable where needed rather than replicate the more complicated code in many places.

%let name = %scan(&names, &i,%str( )) ;

...

VBAR &name ...

...

WHERE name = "&name" ;

...

Also you are working too hard to count the number of names.  First you do not need %EVAL() as the result of the COUNTW() function is already a number.  Second you do not need to count as PROC SQL has already put the count into the automatic variable SQLOBS.

%let num_students = &sqlobs ;

[AncaTilea]

Thank you, Tom.

I am yet to be comfortable with quotes and macro variables.

I was not aware of the &sqlobs.

Thank you.

Cheers.

Anca.

[G_I_Jeff]

I've attached my original SAS code. Possible someone can see in it what I would like to change:

• For every SGPLOT and TABULATE procedure I would like to create a macro that the SYSTEM variable is passed to and loop through all unique values of the SYSTEM variable
• I can get the unique values of SYSTEM from the very first SUMMARY procedure I execute in the code

[hulllc]

What I might suggest is reorganizing the data. 

Create a table with three columns

Student    Test  Score

Alice      1      100

Alice       2      97.3

Carrie      1      88.7

Frank       1      79.8

Frank       2      77

...etc.

You can add as many rows to this table as you want. You can easily append new rows while storing the existing

data in a SAS data set, and not have to reread the existing data every time.

If you want to print a report, this table can easily be transposed on Student with test as the new column title with "Test " as a prefix. This will produce a wide table.

You can use By statements in many SAS procedures to analyze this data set by test or by student. You could plot x = test y = score by student, or x = student, y = score by test without rearranging the data.

No macros needed.