Something strange, is there any one can help?

shiyiming

I am trying to get the transaction information of certain period from data warehouse. In order to make it more efficient, I wrote a macro as


%macro RFM /store;
libname out "&directory";
data RFM (keep= account_key customer_key merchant_key txn_key txn_date txn_date_key
                txn_amount);
  set dwh.central_card_sale_fact;
  txn_date=datepart(txn_date_key);
  where txn_key in (1,2,5,6,7,8,9,10,11,12,13,14,72,73,74,75,84,85,86,87,88,89,90,
                    91,92,93,95,96,97,98,1010,1014)
  and txn_amount not in (.)
  and account_key not in (.)
  and merchant_key not in (.)
  and &start<=datepart(txn_date_key)<=&end;
run;
*........... other coding ....... *;
%mend;
%let diretory=D:\;
%let start='01Oct2002'd;
%let end='31Dec2002'd;
%Rfm;

The strange thing is after I run the whole code, I found all transaction information available in the datawarehouse is captured by data RFM, not just the period I need. Any one can tell me why this happens? Thanks a lot!

Hanna

shiyiming

Really! very very strange.

No error was found by me from above codes.I suggest to install lastest SAS hotfixes.If it will not be resolved after that,please give us more information,such as the contents in the log window.

shiyiming

I checked the log a lot of times, no error information at all.

shiyiming

I have no ideas bacause I'm not at the side of you.(^_^)

You should test the codes step by step,start from one 'where' condition, and then two 'where' condition.

You should directly use data step instead of macro block and direct date value assignation instead of macro variables assignation.

Perhaps it's caused by other factors, don't only foucs on one point, check your data and other parts of your codes.

Good luck! Caution: Shiyiming can't be freely used.

shiyiming

haha, <!-- s:D --><img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /><!-- s:D -->  actually already solve the problem with some other solutions, as you mentioned: use data steps directly, or use this way

%macro RFM (startyear,startmonth,startday,endyear,endmonth,endday) /Store;
libname out "&amp;directory";
%let start=%sysfunc(mdy(&amp;startmonth,&amp;startday,&amp;startyear));
%let end=%sysfunc(mdy(&amp;endmonth,&amp;endday,&amp;endyear));

......
All the other where in the code works well. So must because the macro variable I used. But still confuse why it doesn't work.  <!-- s:roll: --><img src="{SMILIES_PATH}/icon_rolleyes.gif" alt=":roll:" title="Rolling Eyes" /><!-- s:roll: -->

Thanks any way,

P.S. I am in a business trip(not in China now). And shiyiming is not around in office these days. Normally, he is very helpful when I have questions.
<!-- s:oops: --><img src="{SMILIES_PATH}/icon_redface.gif" alt=":oops:" title="Embarassed" /><!-- s:oops: -->

shiyiming

靠，偶真是脸都红了 <!-- s:oops: --><img src="{SMILIES_PATH}/icon_redface.gif" alt=":oops:" title="Embarassed" /><!-- s:oops: -->

的确很忙，连续2周将不在，不好意思。

shiyiming

Hannaqiu,

The following may be better for improving your macro:

[code:a9743]data test;
        do i =1 to 100;
                dt ='01Oct2002'd + uniform &#40;123&#41;* 365&#46;25;
                dtm = dhms&#40;dt,30,30,30&#41;;
                format dtm datetime&#46; ;
                output;
        end;
run;

options mstored sasmstore =sasuser;
%macro rfm &#40;stdt, endt, indata=work&#46;test, dir=d&#58;\&#41;/store;
libname out &quot;&amp;dir&quot;;
data out&#46;rfm;
        set &amp;indata;
        where &amp;stdt&lt;=datepart&#40;dtm&#41;&lt;=&amp;endt;
run;
%mend rfm;

%rfm&#40;'01Oct2002'd, '31Dec2002'd&#41;;[/code:a9743]

Also, have you checked the Log for the WHERE statement and out dataset, like the two NOTEs below?

[color=blue:a9743]NOTE: There were 24 observations read from the data set WORK.TEST.
      WHERE ('01OCT2002'D&lt;=DATEPART(dtm)) and (DATEPART(dtm)&lt;='31DEC2002'D);
NOTE: The data set OUT.RFM has 24 observations and 3 variables.[/color:a9743]

shiyiming

ya, the log exact looks like that. I tried some other ways also

%macro RFM (startyear,startmonth,startday,endyear,endmonth,endday) /Store;
libname out "&amp;directory";
%let start=%sysfunc(mdy(&amp;startmonth,&amp;startday,&amp;startyear));
%let end=%sysfunc(mdy(&amp;endmonth,&amp;endday,&amp;endyear));

......

Still doesn't work. SAS get all the available obs at the first step. But if I don't use macro, everything seems fine. Is it because of the macro? If so, what kind of format should I use for this purpose? thanks a lot!