DATA Step, Macro, Functions and more

Error in proc SQL with Macro Variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Error in proc SQL with Macro Variable

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


Accepted Solutions
Solution
‎04-12-2012 03:00 PM
Super Contributor
Posts: 1,636

Re: Error in proc SQL with Macro Variable

Hi,

try this:

proc sql;

create table test as

select ScID from dailyRun

where ScID="&mySCID";

quit;

Linlin

View solution in original post


All Replies
Solution
‎04-12-2012 03:00 PM
Super Contributor
Posts: 1,636

Re: Error in proc SQL with Macro Variable

Hi,

try this:

proc sql;

create table test as

select ScID from dailyRun

where ScID="&mySCID";

quit;

Linlin

Occasional Contributor
Posts: 6

Re: Error in proc SQL with Macro Variable

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

Super Contributor
Posts: 1,636

Re: Error in proc SQL with Macro Variable

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;

Occasional Contributor
Posts: 6

Re: Error in proc SQL with Macro Variable

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.

Super Contributor
Posts: 1,636

Re: Error in proc SQL with Macro Variable

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

Occasional Contributor
Posts: 6

Re: Error in proc SQL with Macro Variable

Thank you. I really appreciate the help.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 701 views
  • 0 likes
  • 2 in conversation