Help with array macro (or possibly better way)?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

Help with array macro (or possibly better way)?

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


Accepted Solutions
Solution
‎09-19-2013 10:21 AM
Super User
Super User
Posts: 7,039

Re: Help with array macro (or possibly better way)?

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;

View solution in original post


All Replies
Super Contributor
Posts: 543

Re: Help with array macro (or possibly better way)?

Hi.

What are you trying to accomplish?

Smiley Happy

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

Frequent Contributor
Posts: 75

Re: Help with array macro (or possibly better way)?

Posted in reply to AncaTilea

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

Super Contributor
Posts: 543

Re: Help with array macro (or possibly better way)?

Is this what you want?

proc transpose data = tests out = foo;

    var score:;

    id name;

run;

Super User
Super User
Posts: 7,039

Re: Help with array macro (or possibly better way)?

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

Super User
Posts: 5,497

Re: Help with array macro (or possibly better way)?

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.

Frequent Contributor
Posts: 75

Re: Help with array macro (or possibly better way)?

Posted in reply to Astounding

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.

Frequent Contributor
Posts: 75

Re: Help with array macro (or possibly better way)?

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

Solution
‎09-19-2013 10:21 AM
Super User
Super User
Posts: 7,039

Re: Help with array macro (or possibly better way)?

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;

Frequent Contributor
Posts: 75

Re: Help with array macro (or possibly better way)?

Tom,

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

Jeff

Super Contributor
Posts: 543

Re: Help with array macro (or possibly better way)?

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();

Super User
Super User
Posts: 7,039

Re: Help with array macro (or possibly better way)?

Posted in reply to AncaTilea

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 ;

Super Contributor
Posts: 543

Re: Help with array macro (or possibly better way)?

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.

Frequent Contributor
Posts: 75

Re: Help with array macro (or possibly better way)?

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
Attachment
Occasional Contributor
Posts: 9

Re: Help with array macro (or possibly better way)?

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 464 views
  • 0 likes
  • 5 in conversation