SAS中文论坛

标题: Something strange, is there any one can help? [打印本页]

作者: shiyiming 时间: 2004-7-13 12:36
标题: Something strange, is there any one can help?
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 时间: 2004-7-13 14:38
标题: ?
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 时间: 2004-7-13 14:53
标题: re
I checked the log a lot of times, no error information at all.

作者: shiyiming 时间: 2004-7-13 15:50
标题: hi,mm
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 时间: 2004-7-13 17:07
标题: RE:
haha, <img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /> 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 "&directory";
%let start=%sysfunc(mdy(&startmonth,&startday,&startyear));
%let end=%sysfunc(mdy(&endmonth,&endday,&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. <img src="{SMILIES_PATH}/icon_rolleyes.gif" alt=":roll:" title="Rolling Eyes" />

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.
<img src="{SMILIES_PATH}/icon_redface.gif" alt=":oops:" title="Embarassed" />

作者: shiyiming 时间: 2004-7-13 18:58
标题: RE
靠，偶真是脸都红了 <img src="{SMILIES_PATH}/icon_redface.gif" alt=":oops:" title="Embarassed" />

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

作者: shiyiming 时间: 2004-7-14 10:03
标题: improved
Hannaqiu,

The following may be better for improving your macro:

[code:a9743]data test;
do i =1 to 100;
dt ='01Oct2002'd + uniform (123)* 365.25;
dtm = dhms(dt,30,30,30);
format dtm datetime. ;
output;
end;
run;

options mstored sasmstore =sasuser;
%macro rfm (stdt, endt, indata=work.test, dir=d:\)/store;
libname out "&dir";
data out.rfm;
set &indata;
where &stdt<=datepart(dtm)<=&endt;
run;
%mend rfm;

%rfm('01Oct2002'd, '31Dec2002'd);[/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<=DATEPART(dtm)) and (DATEPART(dtm)<='31DEC2002'D);
NOTE: The data set OUT.RFM has 24 observations and 3 variables.[/color:a9743]

作者: shiyiming 时间: 2004-7-14 22:10
标题: RE
ya, the log exact looks like that. I tried some other ways also

%macro RFM (startyear,startmonth,startday,endyear,endmonth,endday) /Store;
libname out "&directory";
%let start=%sysfunc(mdy(&startmonth,&startday,&startyear));
%let end=%sysfunc(mdy(&endmonth,&endday,&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!

欢迎光临 SAS中文论坛 (http://mysas.net/forum/)