The script uses the INTO :macro_var SEPARATED BY clause of PROC SQL to create a space-separated list of values from the Name column of SASHELP.CLASS. It also retrieves the number of items via the automatic SQLOBS variable.
Data Analysis
Type : SASHELP
The data comes from the SASHELP.CLASS example table.
1 Code Block
PROC SQL
Explanation :
Execute an SQL query to concatenate distinct names into the macro variable STUDENT_LIST and store the number of results in NUM_STUDENTS.
Execute an SQL query to concatenate distinct names into the macro variable STUDENT_LIST and store the number of results in NUM_STUDENTS.
Copied!
| 1 | PROC SQL noprint; |
| 2 | select distinct name into :STUDENT_LIST separated BY ' ' |
| 3 | from sashelp.class; |
| 4 | %let NUM_STUDENTS = &sqlobs; |
| 5 | QUIT; |
2 Code Block
GLOBAL STATEMENTS
Explanation :
Display the content of the generated macro variables in the log for verification.
Display the content of the generated macro variables in the log for verification.
Copied!
| 1 | * Verify the contents of the new macro variables by printing to the SAS log.; |
| 2 | options nosource; |
| 3 | %put ======================; |
| 4 | %put Number of Students: &NUM_STUDENTS; |
| 5 | %put Student List: &STUDENT_LIST; |
| 6 | %put ======================; |
| 7 | options SOURCE; |
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
Copyright Info : Instructor : Josh Horstman
Expert Advice
Stéphanie
Spécialiste Machine Learning et IA.
« In the world of SAS development, the ability to bridge the gap between dataset values and macro variables is the cornerstone of Data-Driven Programming. The PROC SQL approach demonstrated here is the most efficient way to transform column data into a flexible macro list. »