Variable created by %Let is character. I need to to be numeric so I can use it in PROC SQL to

[Longimanus]

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; 

[PeterClemmensen]

What does the log say?

[Longimanus]

Well ... this:

NOTE: Remote submit to LEV3 commencing.
69 %let datumin5digits = 18352;
70 %let cc = se;
71
72 %let SYSLAST = %nrquote(dds_&cc..FINANCIAL_ACCOUNT_CHNG);


73 proc datasets lib = work nolist nowarn memtype = (data view);
74 delete W5FEMUWG_FAC;
75 quit;

NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


76 proc sql;
77 create table work.W5FEMUWG_FAC as
78 select
79 ACCOUNT_RK
80 format = BEST12.
81 informat = BEST12.
82 label = 'Account Key',
83 BALANCE_AMT
84 format = DOLLAR22.2
85 informat = DOLLAR22.2,
86 X_DEBIT_INTEREST
87 label = 'Debit Interest'
88 from &SYSLAST
89 where (&x_target_run_dt Between DATEPART(VALID_FROM_DTTM) And DATEPART(VALID_TO_DTTM))
-
22
WARNING: Apparent symbolic reference X_TARGET_RUN_DT not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT,
SUBSTRING, USER.

[Longimanus]

Ok ... here it just says: NOT resolved. But I had a problem earlier SAS complained it was character. Or I am just confused. The thing is I need to run this for various programs for different dates. I need this &x_target_run_dt variable.

[PeterClemmensen]

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;

[Kurt_Bremser]

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
      

[ballardw]

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.

[Longimanus]

Ok wait ... please wait. I did run something weird. I'll be back!

[Longimanus]

I solved it. Sorry! 🙂 

[PeterClemmensen]

No problem. See my other answer. PROC SQL should be able to handle this. Glad you found your answer 🙂

[Tom]

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.