topic Re: where clause operator incompatible variables in New SAS User

where clause operator incompatible variables

Sathiskumar_D — Tue, 23 Apr 2019 02:56:44 GMT

options symbolgen mlogic;

%macro accountreport (State=,Age=,Last_Tran_Date=);
%let title=Detail Listing of Account;
data profile3;
set myprj2.profile2 (Keep=Acct_ID Name birthday State Balance Last_Tran_Date);
Age=((int(today()-birthday)/365.25));
format Last_Tran_Date year4.;
run;

/*proc contents data=profile3;
run;*/
proc summary data=profile3;
where Age="&Age" and Last_Tran_Date="&Last_Tran_Date";
var Acct_ID Name Age Balance Last_Tran_Date;
output out=summarized_data
sum=Balance;
run;
proc print data=summarized_data;
title "&title";
run;
%mend accountreport;

%accountreport(Last_Tran_Date=2015);

I don't understand the error message.I checked the proc contents too..

Re: where clause operator incompatible variables

heffo — Tue, 23 Apr 2019 03:07:28 GMT

I assume that the variable Age is numeric and that the variable Last_Tran_Date is as SAS date. Then this where statement is not correct. You can't compare string variables to numeric variables.

where Age="&Age" and Last_Tran_Date="&Last_Tran_Date";

So, remove the quotes around your macro variables. Also, you accept what I guess is a year for the last_tran_date, but you have to make sure to compare the date with a year. You can use the year function for this. It should probably look something like:

where 
Age=&Age and 
year(Last_Tran_Date) = &Last_Tran_Date;

Of course, you also have to make sure that there is a value for both age and last_tran_date. Possibly by adding it in the header.

%macro accountreport (State=,Age=.,Last_Tran_Date=.);

Re: where clause operator incompatible variables

Sathiskumar_D — Tue, 23 Apr 2019 03:12:02 GMT

it is still showing error.

Re: where clause operator incompatible variables

heffo — Tue, 23 Apr 2019 03:15:45 GMT

What is the error? Did you run the whole code, including the macro definition (%macro to %mend) to make sure you have recompiled the macro?

Would there be any results when you have age as missing and the year set to 2015?

Re: where clause operator incompatible variables

Sathiskumar_D — Tue, 23 Apr 2019 03:18:06 GMT

variable name in the list does not match type prescribed in this list..

Does it mean var statement will not accept any char variable???

Re: where clause operator incompatible variables

Sathiskumar_D — Tue, 23 Apr 2019 03:52:40 GMT

Yes..I did..It is still showing error

Re: where clause operator incompatible variables

Astounding — Tue, 23 Apr 2019 03:53:26 GMT

That's right. SAS won't try to compute the sum of a character variable. If you ask for that, you get an error message.

Re: where clause operator incompatible variables

Sathiskumar_D — Tue, 23 Apr 2019 03:56:45 GMT

I need the sum for balance not for any character variable..

Re: where clause operator incompatible variables

Astounding — Tue, 23 Apr 2019 04:05:58 GMT

Then why add all those other variables to the VAR statement? What are you trying to accomplish by doing that?

Re: where clause operator incompatible variables

ballardw — Tue, 23 Apr 2019 16:11:55 GMT

@Sathiskumar_D wrote:

variable name in the list does not match type prescribed in this list..

Does it mean var statement will not accept any char variable???

Post the log with the errors. Since you are working with macro code you should set the option mprint before executing the macro to get more context on the error.

Options mprint;

%accountreport(Last_Tran_Date=2015);

Then go to the LOG and copy the generated code from the start of the lines that show MPRINT though to the end. Paste the copied code into a code box opened with the forum's {I} icon or "running man".

You may need to provide the results of Proc Contents on your data set as well since your code expects one type of variable and your data appears to not match that.

Proc summary will only accept NUMERIC variables on the VAR statement.

I am suspecting that you may have wanted something more like

proc summary data=profile3 nway;
   where Age=&Age and year(Last_Tran_Date)=&Last_Tran_Date.;
   class  Acct_ID Name Age Last_Tran_Date;

   var  Balance ;
   output out=summarized_data
        sum=Balance;
run;

If the intent was to get a sum for the combinations of Acct_id Name and Age Last_Tran_date.

If last_tran_date or age are NOT character variables then do not use quotes around them.

If last_tran_date is an actual date your where statement probable should look like:

where age=&age. and year(last_tran_date) = &last_tran_date. ;

SAS does not use a formatted value for comparisons unless you force it using Put(variable, format.) = "somevalue"

Re: where clause operator incompatible variables

Sathiskumar_D — Tue, 23 Apr 2019 22:41:04 GMT

options symbolgen mlogic;

%macro accountreport (State=,Age=,Last_Tran_Date=);
%let title=Detail Listing of Account;
data profile3;
set myprj2.profile2 (keep=Acct_ID Name Birthday State Balance Last_Tran_Date);
Age=((int(today()-birthday)/365.25));
/*birthdate=input(birthday,mmddyy10.);*/
format Last_Tran_Date year4.
		Age 3.0
		Birthday best.;/*stored in numeric format*/
run;
/*proc print data=profile3;
run;*/
/*proc contents data=profile3;
run;*/
proc print data=profile3;
var Acct_ID Name Age Balance Last_Tran_Date; 
sum Balance;
where Age=&Age and year(Last_Tran_Date)=&Last_Tran_Date;
title &title;
run;
%mend accountreport;

options mprint;
%accountreport(Last_Tran_Date=2015);

It is still showing ERROR!