Hi there! I am running some tests and am a little (very) rusty. I need to run a simple sql. I want to run this in SAS Base.
I would like the x_target_run_dt variable to be 18352 (the 31MAR2010).
When I use: %let x_target_run_dt = 18352;
the &x_target_run_dt will be character and contain '18312'. The VALID_FROM_DTTM and VALID_TO_DTTM columns are numeric and the SQL doesn't like this. How do I fix this?
proc sql;
create table work.W5FEMUWG_FAC as
select
ACCOUNT_RK
format = BEST12.
informat = BEST12.
label = 'Account Key',
BALANCE_AMT
format = DOLLAR22.2
informat = DOLLAR22.2,
X_DEBIT_INTEREST
label = 'Debit Interest'
from lib_dds.tablewithfromandtodates
where (&x_target_run_dt Between DATEPART(VALID_FROM_DTTM) And DATEPART(VALID_TO_DTTM));
quit;
What does the log say?
Seems to me that the macro variable x_target_run_dt does not contain a value? PROC SQL should be able to handle this. See the below example
%let dt=11262;
proc sql;
create table test as
select * from sashelp.stocks
where (&dt between date and date);
quit;
ALWAYS(!!!) debug the first problem first. In your case it's this:
WARNING: Apparent symbolic reference X_TARGET_RUN_DT not resolved.
Your code as such is OK, see this:
data tablewithfromandtodates;
input account_rk balance_amt x_debit_interest valid_from_dttm :datetime19. valid_to_dttm :datetime19.;
format valid_from_dttm valid_to_dttm :datetime19.;
datalines;
1 23 25 01mar2010:00:00:00 30apr2010:23:59:59
;
%let x_target_run_dt = 18352;
proc sql;
create table work.W5FEMUWG_FAC as
select
ACCOUNT_RK
format = BEST12.
informat = BEST12.
label = 'Account Key',
BALANCE_AMT
format = DOLLAR22.2
informat = DOLLAR22.2,
X_DEBIT_INTEREST
label = 'Debit Interest'
from tablewithfromandtodates
where &x_target_run_dt Between DATEPART(VALID_FROM_DTTM) And DATEPART(VALID_TO_DTTM);
quit;
Log:
27 %let x_target_run_dt = 18352; 28 29 proc sql; 30 create table work.W5FEMUWG_FAC as 31 select 32 ACCOUNT_RK 33 format = BEST12. 34 informat = BEST12. 35 label = 'Account Key', 36 BALANCE_AMT 37 format = DOLLAR22.2 38 informat = DOLLAR22.2, 39 X_DEBIT_INTEREST 40 label = 'Debit Interest' 41 from tablewithfromandtodates 42 where &x_target_run_dt Between DATEPART(VALID_FROM_DTTM) And DATEPART(VALID_TO_DTTM); NOTE: Table WORK.W5FEMUWG_FAC created, with 1 rows and 3 columns. 43 quit; NOTE: PROZEDUR SQL used (Total process time): real time 0.02 seconds cpu time 0.00 seconds
You show matching you stated value in the first post of 18352
69 %let datumin5digits = 18352;
And then use in proc sql
89 where (&x_target_run_dt Between DATEPART(VALID_FROM_DTTM) And DATEPART(VALID_TO_DTTM))
Perhaps you don't actually have code assigning a value to &x_target_run_dt , or has a value from previous test code? At least you did not show any code assigning a value to that macro variable.
I solved it. Sorry! 🙂
No problem. See my other answer. PROC SQL should be able to handle this. Glad you found your answer 🙂
Looks like you solved your issue, what ever it was.
But to the question in you subject line:
Macro variables only contain character strings.
How/where you expand the value in your actual SAS code will determine if SAS treats those character strings as numbers or something else.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.