topic Re: using a macro variable doesn't seem to work in proc sql in SAS Programming

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

BRKS — Tue, 26 Feb 2019 17:04:15 GMT

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

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

Reeza — Tue, 26 Feb 2019 17:12:10 GMT

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

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

BRKS — Tue, 26 Feb 2019 17:19:38 GMT

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! 🙂