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

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

Longimanus — Thu, 18 Jul 2019 08:03:10 GMT

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;

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

PeterClemmensen — Thu, 18 Jul 2019 08:00:56 GMT

What does the log say?

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

Longimanus — Thu, 18 Jul 2019 08:04:44 GMT

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.

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

Longimanus — Thu, 18 Jul 2019 08:07:50 GMT

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.

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

Longimanus — Thu, 18 Jul 2019 08:09:56 GMT

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

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

Longimanus — Thu, 18 Jul 2019 08:12:30 GMT

I solved it. Sorry! 🙂

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

PeterClemmensen — Thu, 18 Jul 2019 08:12:42 GMT

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;

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

PeterClemmensen — Thu, 18 Jul 2019 08:13:14 GMT

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

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

Kurt_Bremser — Thu, 18 Jul 2019 08:13:38 GMT

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

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

ballardw — Thu, 18 Jul 2019 15:17:22 GMT

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.

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

Tom — Thu, 18 Jul 2019 15:21:24 GMT

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.