topic Re: How to create a macro variable to subset a dataset in Proc SQL? in SAS Programming

How to create a macro variable to subset a dataset in Proc SQL?

xtan2 — Mon, 15 Mar 2021 14:09:29 GMT

There is a question:

Create a Macro variable for Dallas in the Location variable of Data: ALL.
Use this macro variable to subset the ‘ALL’ excel data inside the proc SQL to select variables student_name,
teacher, fee, days, paid, location. Write a Proc SQL query to find the ‘average fee’ and ‘total fee’
by ‘paid’ variable for Dallas.

This data set of All is like:

Using this following Proc SQL procedure the goal is easily achieved:

Proc SQL;
Select student_name, fee, days, paid, location,
mean(fee) as avg_fee label = 'average fee',
sum(fee) as tot_fee label = 'total fee'
From All
Where location = 'Dallas' and Paid = 'Y';
Quit;

However, when I tried to use the following code to create a macro variable to subset the location variable into 'Dallas', it did not work:

Proc SQL;
Select student_name, fee, days, paid,
mean(fee) as avg_fee label = 'average fee',
sum(fee) as tot_fee label = 'total fee', location into :Dallas
From All
Where Paid = 'Y';
Quit;

Would someone like to kindly show me how I should create the macro variable so as to subset the location variable successfully?

Thank you!

Re: How to create a macro variable to subset a dataset in Proc SQL?

Athenkosi — Mon, 15 Mar 2021 14:52:53 GMT

Hello,

Why do you want to create a macro variable if you know you want to filter by Dallas?

Re: How to create a macro variable to subset a dataset in Proc SQL?

xtan2 — Mon, 15 Mar 2021 15:11:22 GMT

Hello. Athenkosi,

This is because I have to satisfy what the instructor wants.

The question is

Create a Macro variable for Dallas in the Location variable of Data ALL.
Use this macro variable to subset the ‘ALL’ excel data inside the proc SQL to select variables student_name,
teacher, fee, days, paid, location. Write a Proc SQL query to find the ‘average fee’ and ‘total fee’
by ‘paid’ variable for Dallas.

Re: How to create a macro variable to subset a dataset in Proc SQL?

xtan2 — Mon, 15 Mar 2021 15:46:48 GMT

I got it! Also, I correct a mistake:

The data set should be classified by 'Pait', not 'Paid' = 'Y'.

%let loctn = Dallas;
Proc SQL;
Select student_name, fee, days, paid, location,
mean(fee) as avg_fee label = 'average fee',
sum(fee) as tot_fee label = 'total fee'
From All
Where location = "&loctn"
Group by paid;
Quit;