date format

[Geo-]

Anyone knows how to fix this macro?It probably do with date format problem. In the attachment is data set sample .thank you for your time.

%macro sum_txn_inf(stat_dt);
%let mbeg=%sysfunc(intnx(month,&stat_dt.,0,begin),data9.);
%let mend=%sysfunc(intnx(month,&stat_dt.,0,end),data9.);
%let mon=%sysfunc(putn(&stat_dt.,yymmn4.));

data _txn_dtl_inf;
	if _n_=0 then set acct_list;
	if _n_=1 then do;
		declare hash hh(dataset:"acct_list");
		hh.definekey("acct");
		hh.definedata("acct");
		hh.definedone();
	end;

	set CCM_BASE_TRANS(keep=stat_date eff_date txn_type txn_amt_rmb
						where=("&mbeg. :00:00:00"dt le stat_date le "&mend. :00:00:00"dt
						and "&mbeg. :00:00:00"dt le eff_date le "&mend. :00:00:00"dt));
	format eff_dt yymmdd10.;
	eff_dt=datepart(eff_date);
	if hh.find()=0;
run;

proc summary data=_txn_dtl_inf nway missing;
	class eff_date txn_type;
	var txn_amt_rmb;
	output out=_txn_type_sum(drop=_:)
		sum=txn_amt_&mon.;
run;

proc sort data=_txn_type_sum out=txn_type_sum_&mon.;
	by eff_dt txn_type;
run;

%mend;

%sum_txn_inf("01may2015"d);

acct_list:

acct

1

2

3

4

[novinosrin]

i see a typo

%let mbeg=%sysfunc(intnx(month,&stat_dt.,0,begin),data9.);
%let mend=%sysfunc(intnx(month,&stat_dt.,0,end),data9.);

in the above data9. should be date9.

[SASKiwi]

%let mbeg=%sysfunc(intnx(month,&stat_dt.,0,begin),date9.);
%let mend=%sysfunc(intnx(month,&stat_dt.,0,end),date9.);

Getting rid of these blanks should help:

where=("&mbeg.:00:00:00"dt le stat_date le "&mend.:00:00:00"dt
and "&mbeg.:00:00:00"dt le eff_date le "&mend.:00:00:00"dt

[Geo-]

there is still an error"could not find variable eff_dt" in proc sort statement.Why ?

[PGStats · Accepted solution]

The variable is called eff_date in the proc summary step.