Please check SAS 9.4 ADV s106d02.sas demo. Step 2 code had an error message no table is created

[hmlong25]

s106d02.sas Concatenating Values in Macro Variables.sas

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

* Demo *;
* 1) Open the s106d02.sas program in the demos folder *;
* and find the Demo section. Run the code in Step 1: *;
* Create Macro Variables. Examine the results and log *;
* to see the values of the newly created macro *;
* variables &Division and &StateList. *;
* 2) Discuss the query under Step 2: Use Macro *;
* Variables. The table being created will end with *;
* the value of the macro variable &Division. The *;
* customer table will attempt to be subset by a list *;
* of values from the &StateList macro variable. Run *;
* the query and examine the errors. *;
* Note: The name of the new table will end with the *;
* value of the macro variable. *;
* 3) Move back to Step 1. Add the QUOTE function around *;
* the Name column. Run the query and %PUT statements. *;
* Examine the results and log. *;
* 4) Add the STRIP function inside the QUOTE function. *;
* Run the query and %PUT statements. Examine the *;
* results and log. *;
* 5) Move to Step 2. Run the query. Examine the results *;
* and log. *;
* 6) Move to Step 1. Change the value in the %LET *;
* statement to 9 and add the NOPRINT option in the *;
* PROC SQL statement to finalize the program. Run the *;
* entire program. Examine the results and log. *;
***********************************************************;

**************************************************;
* 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;

[Tom]

That sounds like the PURPOSE of the exercise.  Look at what it says to do in bullet point number (3).

If you do not include the quotes in the macro variable.

%let statelist=CT,MA,NY;

And then try to use it to generate code like:

where state in (&statelist)

You get code like:

where state in (CT,MA,NY)

Which is invalid because written that way CT, MA and NY are variable names.  But the IN operator only wants constants in the list, not variable names.

If you add the QUOTE() function in the initial query then the generated WHERE condition will instead look like:

where state in ("CT","MA","NY")

Which is valid SAS syntax.  And it should work as long as STATE is a character variable in the dataset being queried.

[hmlong25]

I did add the quote formatting and that was not the error message I was referring to.

Two different tables were cited in the Demo instructions by mistake.  SQ.Statepopulation in Step one and then SQ.Customer in Step 2.  when I changed the table in Step 2 to match Step 1 ( SQ.StatePopulation).... I did get  a table created in Step 2. 

 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;

[Cynthia_sas]

Hi:

  I'm not sure why you're getting errors. I downloaded the zip file for the SQL 1 class, created the data files and first ran the s106d02.sas program (after making the required changes) as shown below:

Then I changed the %LET for DIVISION to 9 and ran again.

I got both work.Division3 and work.Division9 created correctly from the sq.customer data file based on first creating the &StateList macro variable created from sq.statepopulation.  Hope this helps you figure out what happened. Seeing your entire log -- both steps starting with the %LET and showing all the %PUT output would be very useful.

Cynthia

[hmlong25]

Hi Cynthia,

Thanks for looking at this log!  I don't know how you got it to run with different tables.  

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

proc sql noprint;
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;

LOG  Step 1

%let Division=3;
74
75 proc sql noprint;
76 select quote(strip(Name))
77 into :StateList SEPARATED BY ","
78 from sq.statepopulation
79 where Division = "&Division";
NOTE: Data file SQ.STATEPOPULATION.DATA is in a format that is native to another host, or the file
encoding does not match the session encoding. Cross Environment Data Access will be used, which
might require additional CPU resources and might reduce performance.
80 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

81 %put &=Division;
DIVISION=3
82 %put &=StateList;
STATELIST="IL","IN","MI","OH","WI"
83
84
85 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
97

LOG  Step 2

78 where State in (&StateList);
SYMBOLGEN: Macro variable STATELIST resolves to "IL","IN","MI","OH","WI"
NOTE: Data file SQ.CUSTOMER.DATA is in a format that is native to another host, or the file encoding
does not match the session encoding. Cross Environment Data Access will be used, which might
require additional CPU resources and might reduce performance.
ERROR: Some character data was lost during transcoding in the dataset SQ.CUSTOMER. Either the data
contains characters that are not representable in the new encoding or truncation occurred
during transcoding.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
79 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.12 seconds
cpu time 0.11 seconds

80 options nosymbolgen;
81
82 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
94