Please check why step two has an error message...Thanks!

[hmlong25]

********************************************************;
* Concatenating Values in Macro Variables *;
***********************************************************;

***********************************************************;

**************************************************;
* Step 1: Create Macro Variables *;
**************************************************;
%let Division=3;

proc sql;
select quote(strip(Name))
into :StateList SEPARATED BY ","
from sq.statepopulation
where Division = "&Division";
quit;
%put &=Division;
%put &=StateList;

**************************************************;
* Step 2: Use Macro Variables *;
**************************************************;
options symbolgen;
proc sql;
create table division&Division as
select *
from sq.customer
where State in (&StateList);
quit;
options nosymbolgen;

[PeterClemmensen]

What is the error message? What does &statelist contain?

[Tom]

You did not share the log, so it is hard to tell. What type of variable is DIVISION?  How many values of NAME did your first query find?  

Just looking at the code here are the things that look like mistakes (or at least might cause trouble).

Is DIVISION numeric or character in sq.statepopulation?  Your code is treating it as character.  If it is numeric remove the quotes from your WHERE condition. If it is character you might need to worry that the values in the dataset have leading spaces. ' 3' is not the same as '3'.

Why do you remove the leading spaces from NAME in your first query?  Doing that will mean the values in the macro variable list might not match the original values.  If STATE in the other dataset could also have leading spaces you might need to use LEFT() (or STRIP()) to remove the leading spaces when testing the values against your macro variable list.  Do the same values of NAME appear more than once?  That could make the macro variable longer than it needs to be.

It is usually safer to use single quotes to quote values you are putting into macro variables. Those will prevent the macro processor from trying to resolve any & or % characters that might be in the strings.

Macro variables are easier to deal with when they don't have commas in them.  Since the IN operator in SAS will accept either space or comma as the delimiter just use space when generating the macro variable.

Not sure why the second step is using SQL.  Why not just use a normal data step?

proc sql noprint;
select distinct quote(trim(Name),"'")
  into :StateList SEPARATED BY ' '
  from sq.statepopulation
  where Division = "&Division"
;
quit;

data division&division ;
  set sq.customer;
  where state in (&statelist);
run;

[Cynthia_sas]

Hi:

  "SQ" is the library reference we use in our SQL 1 course. So if the student is taking the SQL 1 course as self-paced e-learning, we need to know what the Lesson/Section/Practice is that the student is doing. Or, the student could post their question in the Learn --> SAS Training --> Advanced Programming forum if their question is about one of our e-learning classes.

Cynthia

In checking further, it looks like this is code from one of the demos in Lesson 6:

  If the code that the student runs gets an error message, we need to see the error message in the SAS Log. If you watch the demo in the class, you'll see that the instructor has a %LET statement and the narration explains the use of &DIVISION and how it is initially created with a value of 3. The first time this demo is run the instructor explains why the code generates an error. Then he continues with the demo, making changes and he changes the value for DIVISION from 3 to 9:

  If the student is trying to repeat the demo, then the student needs to make the same changes in their code that are shown in the demo.