Error in proc SQL with Macro Variable

[bman]

Looking for a solution to an isue when I use Macro variables in a proc sql statement.  Here's what I'm doing.

The dataset(dailyRun) I'm working with has a field called ScID. This is character field with a length of 10

The code;

Prompt manager has a prompt called mySCID that will be filled in by the user when they run the code.

code to create a table

proc sql;

create table test as

select ScID from dailyRun

where ScID=&mySCID;

quit;

I get the following error "expression using equals(=)  has components that are different data types"

The &mySCID variable has 00100200 as the value.  If  I change the code and place the value '00100200' it works.

proc sql;

create table test as

select ScID from dailyRun

where ScID='00100200';

[Linlin · Accepted solution]

Hi,

try this:

proc sql;

create table test as

select ScID from dailyRun

where ScID="&mySCID";

quit;

Linlin

[bman]

thanks that worked.  What does the double quotes do when SAS reads it. Does it convert the macro variable?

[Linlin]

When comparing character variable with a value, we have to add “ “ to the value.

It is the same as “if sex="F";” in the code below:

data want;

set sashelp.class;

if sex="F" ;

run;

[bman]

Thanks. How would it work for a date prompt. I know when I have to do  a date value i place '01Apr2011',d when the field I'm checking against is a date field.  If I use a prompt for that and the user places 01apr2011 or 04012011 would I just put that variable in "" too?  I really appreciate your help. I'm new to SAS coding.

[Linlin]

yes, you have to put the macro variable in " ".  example:

%let dt=01apr2011;

data have;

format date mmddyy10.;

input date mmddyy8.;

cards;

03012011

04012011

;

data want;

set have;

if date="&dt"d;

run;

proc print;run;

Linlin

[bman]

Thank you. I really appreciate the help.