Using & variable to automate CAT

[Neeydchi]

Hi! I'm trying to automate a piece of code that works well.

My goal is to list all the students who did an activity, but I need to report this on a list within my current table. So, I use the following code:

data students_list;
set WORK.students;
SP='; ';
students=catx(sp, of STUDENT_1-STUDENT_45);
run;

PROC SQL;
Create Table final as
select t1.*, t2.students from activity t1, students_list t2;
Quit;

But I don't want to have to indicate the number of students manually, as this has caused me to generate many errors. So I'm trying to do it with the following code, but it's not working. Can someone help me?

Attempt using &n_students:

PROC SQL noprint;
	SELECT  count(*) 
	INTO :n_students
	FROM WORK.class_activity;
	QUIT;

data students_list;
set WORK.students;
SP='; ';
students=catx(sp, of STUDENT_1-STUDENT_&n_students);
run;

PROC SQL;
Create Table final as
select t1.*, t2.students from activity t1, students_list t2;
Quit;

[Reeza · Accepted solution]

students=catx(sp, of STUDENT_:);

Depends on your naming structure to some degree. This blog post covers some of the options and how they work:

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

---

@Neeydchi wrote:

Hi! I'm trying to automate a piece of code that works well.

My goal is to list all the students who did an activity, but I need to report this on a list within my current table. So, I use the following code:

data students_list;
set WORK.students;
SP='; ';
students=catx(sp, of STUDENT_1-STUDENT_45);
run;

PROC SQL;
Create Table final as
select t1.*, t2.students from activity t1, students_list t2;
Quit;

But I don't want to have to indicate the number of students manually, as this has caused me to generate many errors. So I'm trying to do it with the following code, but it's not working. Can someone help me?

Attempt using &n_students:

PROC SQL noprint;
	SELECT  count(*) 
	INTO :n_students
	FROM WORK.class_activity;
	QUIT;

data students_list;
set WORK.students;
SP='; ';
students=catx(sp, of STUDENT_1-STUDENT_&n_students);
run;

PROC SQL;
Create Table final as
select t1.*, t2.students from activity t1, students_list t2;
Quit;

---

[Tom]

But how did you get to have multiple "student" variables to begin with?

Why isn't the data organized to have one variable for student and multiple observations if there are multiple students?