topic Error in proc SQL with Macro Variable in SAS Programming

Error in proc SQL with Macro Variable

bman — Thu, 12 Apr 2012 18:41:11 GMT

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

Re: Error in proc SQL with Macro Variable

Linlin — Thu, 12 Apr 2012 19:00:17 GMT

Hi,

try this:

proc sql;

create table test as

select ScID from dailyRun

where ScID="&mySCID";

quit;

Linlin

Re: Error in proc SQL with Macro Variable

bman — Thu, 12 Apr 2012 19:10:50 GMT

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

Re: Error in proc SQL with Macro Variable

Linlin — Thu, 12 Apr 2012 19:24:16 GMT

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;

Re: Error in proc SQL with Macro Variable

bman — Thu, 12 Apr 2012 20:27:53 GMT

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.

Re: Error in proc SQL with Macro Variable

Linlin — Thu, 12 Apr 2012 22:09:38 GMT

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

Re: Error in proc SQL with Macro Variable

bman — Thu, 12 Apr 2012 22:34:18 GMT

Thank you. I really appreciate the help.