topic Re: %let statement problem in SAS Procedures

%let statement problem

deleted_user — Mon, 13 Jul 2009 07:24:46 GMT

i have this simple program which is giving me a headach, this variable mm_bdate resolves correctly to '01May09'd, when i use it in a PUT statement... but when the same variable when used in select statement it resolves to '&v_bddmmmyy'd and gives me eros.. please help here.... i have copied my program and the output from the log file for reference... thanks

libname ext '/home/nbk6cst/Newapp';

%let v_bddmmmyy=01May09 ;

%let mm_bdate = '&v_bddmmmyy'd;

data _null_;
put "The value of Current Month is : &mm_bdate";

Proc sql;
create table ext.cfc_loss_amt as
select
accno,
sum(chargeoff) as chargeoff,
sum(contra_chargeoff) as contra_chargeoff,
sum( recovery) as recovery,
sum(net_charge_off_s) as net_Charge_off_s
from ext.loss
where product not in ('CL_PRM') and ( month >= &mm_bdate)
group by accno
order by accno;
quit;
run;

From LOG
---------------

8 put "The value of Current Month is : &mm_bdate";
9
10

The value of Current Month is : '01May09'd

19 from ext.loss
20 where product not in ('CL_PRM') and ( month >= &mm_bdate.)
ERROR: Invalid date/time/datetime constant '&v_bddmmmyy'd.
21 group by accno
22 order by accno;

Re: %let statement problem

DanielSantos — Mon, 13 Jul 2009 09:35:53 GMT

Single quote versus double quote problem.

You should be aware that nothing gets resolved between single quotes.

put "The value of Current Month is : &mm_bdate";

is resolved because expression is between double quotes.

...( month >= &mm_bdate)

is not resolved because expression is between single quotes.

so, try this instead:

%let mm_bdate = "&v_bddmmmyy"d;

and loose your put statement (to avoiding double double quoting problems).

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

Re: %let statement problem

deleted_user — Mon, 13 Jul 2009 11:59:32 GMT

But thing is i need the single quote too... i mean i want mm_bdate to have the value '01May09'd (with the single quote) ... how do i do this i have tried like this

%let mm_bdate = "'&v_bddmmmyy'"d;

i got this result

8 put "The value of Current Month is : &mm_bdate";
9
10

The value of Current Month is : 01May09d

single quotes was missing.. how do i solve this problem?

Re: %let statement problem

Cynthia_sas — Mon, 13 Jul 2009 15:52:05 GMT

Hi:
It is generally a bad idea to "prequote" your macro variable values in the %LET statement. The quotes belong to the WHERE clause...

This is perfectly valid SAS code:

[pre]
where birthday = "15Nov1950"d;
%let bday = 15Nov1950;
where birthday = "&bday"d;
[/pre]

the quotes around your date constant can be either single or double quotes. Putting the quotes, where they belong, in the WHERE statement or
clause, allows you to avoid "pre-quoting".

In your original post, you showed a where clause. You wanted quotes in &MM_BDATE:
[pre]
where product not in ('CL_PRM') and ( month >= &mm_bdate)
[/pre]

But, you could just use your &V_BDDMMMYY macro variable with quotes in the WHERE clause or, if you needed to compare a month value to month value, use the macro variable with %SYSFUNC:

[pre]
where product not in ('CL_PRM') and ( month >= "&v_bddmmmyy"d)
OR
where product not in('CL_PRM') and
month >= %sysfunc(month("&v_bddmmmyy"d) )

[/pre]

Also, you do not need a DATA _NULL_ to show the value of a macro variable. In addition to the regular PUT statement, there is a %PUT statement, which can be used either inside or outside the DATA _NULL_ environment, right after your %LET statements, for example.

Using %PUT is a great way to verify that your manipulation and setting of macro variables is happening correctly.

For example, if you issued the %LET and %PUT statements shown in the below set of code, you would see the results as I have pasted them from my SAS Log. Note that I do not have a DATA _NULL_ program.

cynthia

[pre]
SAS LOG:
38 %let v_bddmmmyy=01May09 ;
39
40 %let mm_bdate = '&v_bddmmmyy'd;
41
42 %let dq_bdate = "&v_bddmmmyy"d;
43
44 %put The value of mm_bdate is : &mm_bdate;
The value of mm_bdate is : '&v_bddmmmyy'd
45
46 %put The value of dq_bdate is: &dq_bdate;
The value of dq_bdate is: "01May09"d
47
48 %put the value of v_bddmmmyy is: &v_bddmmmyy;
the value of v_bddmmmyy is: 01May09
49
50 %put the month of v_bddmmmyy is: %sysfunc(month("&v_bddmmmyy"d));
the month of v_bddmmmyy is: 5
51
52 %put the year of v_bddmmmyy is: %sysfunc(year("&v_bddmmmyy"d));
the year of v_bddmmmyy is: 2009
53
54 %put the word month of v_bddmmmyy is: %substr(&v_bddmmmyy,3,3);
the word month of v_bddmmmyy is: May

[/pre]

Re: %let statement problem

deleted_user — Mon, 13 Jul 2009 17:17:51 GMT

but still as you showed in your log .. the value of mm_bdate is still '&v_bddmmmyy'd why wont it substitute the value of the v_bddmmmyy and display it as '01May09'd..... i cant understand that

44 %put The value of mm_bdate is : &mm_bdate;
The value of mm_bdate is : '&v_bddmmmyy'd

when i use this statement

%let mm_bdate = '&v_bddmmmyy'd; here mm_bdate value should assigned only after substituting the value of v_bddmmmyy right? .... please explain whats happening here..

all i want is mm_bdate value to have '01May09'd by substituting the value of v_bddmmmyy .. need to know atleast if there are any workaround for this...

Sorry for the nooby questions.. i am just learning SAS... thanks for all the replies guys, much appreciated 🙂

Re: %let statement problem

Cynthia_sas — Mon, 13 Jul 2009 19:03:11 GMT

Hi:
Macro variables enclosed in SINGLE quotes do not resolve in the macro variable resolution process.

As I said, it is a bad idea to try to "pre-quote" macro variables in your %LET statement. The quotes belong in the WHERE statement, not in the %LET statement.
The quotes for a date constant do NOT have to be single quotes.

If you follow the general rules for creating macro variable references, you would have started with this -working- WHERE statement:

[pre]
WHERE somevar = 'value' and month ge '01May09'd;
--- then you you would have made a macro variable with a %LET and substituted
the macro variable for the text string 01May09 -- that's all.

You want that text string treated as a date constant, so the quotes and the d belong to the WHERE statement.

Then, you would have changed your program to create &MACVAR and then changed the WHERE to:

%let macvar = 01May09;
WHERE somevar = 'value' and month ge "&macvar"d;

---with the quotes in the WHERE Statement, where they belong. You only would have needed to change the single quotes
from the first WHERE statement to double quotes for the second version in order for the macro variable to resolve.
[/pre]

I'm sorry if my previous explanation was not clear. As it says here, in the documentation:
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a001071889.htm
"Macro variable references that are enclosed in single quotation marks are not resolved."

cynthia