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
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;
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
I'm trying to assign an array with the summarized student names so that I can use it through a loop.
Is this what you want?
proc transpose data = tests out = foo;
var score:;
id name;
run;
Macro just generates SAS code. So show the SAS code you want to generate and explain what parts need to vary.
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.
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.
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
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;
Tom,
That's a great example and your using SQL which I haven't attempted yet. I'll give it a shot!
Jeff
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();
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 ;
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.
I've attached my original SAS code. Possible someone can see in it what I would like to change:
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: