using a macro variable doesn't seem to work in proc sql

[BRKS]

Hi all,

I have a program that I'm writing where I want to pull in data that has been added to the oracle dataset since the last time the program was run so that I can adjust paid medical claims to their final values.

To  do this, after the final adjusted claims are processed, I creat a table that contains the max paid date (pd_dt:  date format):

data final.claims_paid_thru_date (keep=pd_dt);
set claims_paid_thru_date;
if _STAT_ = 'MAX';
run;

This dataset has one record, with a pd_dt of Feb 23, 2019:

 

The next time that I run the full program, I create a macro variable using data _null_ to collect that date:

data _null_ ;
set final.claims_paid_thru_date;
call symput('most_recent_pd_dt',pd_dt);
run;

Then I use that date to try and extract everything from the source that has a newer paid date:

proc sql;
create table claims.sql_claims_new as

(select *

from CCW.CLM_LN_MED_MV

where PD_DT gt "&most_recent_pd_dt"
);
quit;

This is the error I get:

ERROR: Expression using greater than (>) has components that are of different data types.

I assume that proc sql can't handle the sas macro variable.

Is there some way that I can get around this so that the program doesn't need me to enter the date by hand each time?

Thanks so  much!

Barb

[Reeza · Accepted solution]

Did you check what's in the macro variable?

It's likely the unformatted value so it will work if you remove the quotation marks. 

proc sql;
create table claims.sql_claims_new as

(select *

from CCW.CLM_LN_MED_MV

where PD_DT gt  &most_recent_pd_dt
);
quit;

Or change how you create the macro variable, I recommend the first option. 

data _null_ ;
set final.claims_paid_thru_date;
call symput('most_recent_pd_dt',put(pd_dt, date9.));
run;

proc sql;
create table claims.sql_claims_new as

(select *

from CCW.CLM_LN_MED_MV

where PD_DT gt "&most_recent_pd_dt"d
);
quit;

---

@BRKS wrote:

Hi all,

I have a program that I'm writing where I want to pull in data that has been added to the oracle dataset since the last time the program was run so that I can adjust paid medical claims to their final values.

To  do this, after the final adjusted claims are processed, I creat a table that contains the max paid date (pd_dt:  date format):

data final.claims_paid_thru_date (keep=pd_dt);
set claims_paid_thru_date;
if _STAT_ = 'MAX';
run;

This dataset has one record, with a pd_dt of Feb 23, 2019:

 

The next time that I run the full program, I create a macro variable using data _null_ to collect that date:

data _null_ ;
set final.claims_paid_thru_date;
call symput('most_recent_pd_dt',pd_dt);
run;

Then I use that date to try and extract everything from the source that has a newer paid date:

proc sql;
create table claims.sql_claims_new as

(select *

from CCW.CLM_LN_MED_MV

where PD_DT gt "&most_recent_pd_dt"
);
quit;

This is the error I get:

ERROR: Expression using greater than (>) has components that are of different data types.

I assume that proc sql can't handle the sas macro variable.

Is there some way that I can get around this so that the program doesn't need me to enter the date by hand each time?

Thanks so  much!

Barb

---

[BRKS]

Thank you so much!

Ugh...  I usually use that macro variable as part of a path name, hence the double quotes...

Sorry for the trouble and thanks!  That worked!  🙂