1.  %let runtime= "&sysdate9.:23:59:59"Dt; Yes, the syntax is good, use %put to check it. 2. "I don't think I need the "put" part" No, you don't need %put, which was showing the contents of your macro variable. 3.  "The value in the macro I posted is used in the sas program to limit the data set by that date." You need to reveal the rules that being used to generate this date, and we can figure out a way to put it into that macro varaible for downstream usage. ... View more

I am not sure if I understand what you need, But the following will pick up the SAS session start time aumatically and put it in a macro variable. %let runtime= "&sysdate9.:&systime"Dt; %put &runtime; Regards, Haikuo ... View more

Well, SQL pass-through is native to Oracle, and it is definitely more efficient. But I know close to nothing to it except some SAS functions will not be compatible. You could use data step by defining the library first: libname yourlibrary oracle user= password= path=' schema=; Haikuo ... View more

/*Current month and last month*/ %macro clean(want=,have=); data &want; set &have; if intnx('month',today(),-1,'b') <= salesdate <= intnx('month',today(),0,'e'); run; %mend; /*last 3 month*/ %macro last3(want=,have=); data &want; set &have; if intnx('month',today(),-3,'b') <= salesdate < intnx('month',today(),0,'b'); run; %mend; Please ditch the previous version that I had suggested, it will not work during the annual conjunctions. Regards, Haikuo ... View more

Yep! Most likely! Also make sure there is a blank in separated by ' '. ... View more

Could we see your log? ... View more

Thanks, Data_null_. And here is the code following his instruction: proc sql; select DISTINCT memname INTO :NAMES SEPARATED BY ' '   from dictionary.columns where libname='WORK' AND memname ? '_means2'   ;   QUIT; proc datasets library=WORK memtype=data;    save &NAMES;    run;    quit; Note: I have use work library as an example. Regards, Haikuo ... View more

Some construct like this may work for you: %macro clean(want=,have=); data &want; set &have; if not missing(salesdate); if month(today())-month(salesdate)<2; run; %mend; %clean (want=,have=) Regards, Haikuo ... View more

Thanks, LinLin. I know my brain stops working on Friday , so bear with me: I am aware of the final outcome of &list, I am just could not figure out why nmiss(name) not working, while you have to use nmiss(',name,')? Thanks, Haikuo ... View more

Ksharp, Thanks for the answer. I was just wondering why it has to be like:  nmiss(',name,') instead of nmiss(name) or nmiss(,name,) or nmiss(,'name',) etc. I know this is the only way that works, but I can't figure it out how this construct be translated to nmiss(sex) as sex ,nmiss(name) as name,nmiss(weight) as weight .... Any reference link will also be appreciated. Haikuo ... View more

I have to confess, this query is one of the most complicated queries I have ever encountered. I have try to apply all of the rules (complicated by themself already) addressed by OP's first post. Here is what I can come up with: data queued; input QueueName $24. acct_num tran_dt :mmddyy8. tran_tm : $8. ; format tran_dt mmddyy10. acct_num 16.; tran_tm1=input(tran_tm,    b8601tm.); tran_dtm=dhms(tran_dt,0,0, tran_tm1); format tran_dtm datetime20.; cards; C-BXX-N-AE-XXXXXXXXXX         1000000000801180  3/4/2012    170458 N-2XX-N-KE-XXXXXXX            1000000000801180  3/4/2012    170458 N-PXX-N-CP-XXXXXXXXXXXXX      2000000000885230  3/3/2012    213205 C-BXX-D-CP-XXXXXXX            3000000000064278  3/6/2012    202257 C-BXX-N-AE-XXXXXXXXXX         3000000000064278  3/7/2012    202257 C-RXX-N-AE-XXXXXXXXXXXXX      3000000000064278  3/7/2012    202257 C-VXX-D-CP-XXXXXXX-XXXX       3000000000064278  3/7/2012    202257 N-BXX-N-CP-XXXXXXXXXXXXX      3000000000064278  3/7/2012    202257 N-BXX-N-CP-XXXXXXXXXXXX       3000000000064278  3/7/2012    202257 C-BXX-D-CP-XXXXXXX            3000000000064278  3/7/2012    202312 C-BXX-N-AE-XXXXXXXXXX         3000000000064278  3/7/2012    202312 C-RXX-N-AE-XXXXXXXXXXXXX      3000000000064278  3/7/2012    202312 C-VXX-D-CP-XXXXXXX-XXXX       3000000000064278  3/7/2012    202312 N-BXX-N-CP-XXXXXXXXXXXXX      3000000000064278  3/7/2012    202312 N-BXX-N-CP-XXXXXXXXXXXX       3000000000064278  3/8/2012    233701 ; run; data worked; input _QueueName $24. _tran_dt :mmddyy8. _tran_tm : $8.  acct_num workedkey _acct_num; format _tran_dt mmddyy8. acct_num 16.; _tran_tm1=input(_tran_tm,  b8601tm.); _tran_dtm=dhms(_tran_dt,0,0, _tran_tm1); QueueName=_QueueName; format _tran_dtm datetime20.; cards; Inbound                       3/8/2012    172000      3000000000064278  11925138      3000000000064278 C-VXX-D-CP-XXXXXXX-XXXX       3/7/2012    202400      3000000000064278  11918651      3000000000064278 N-PXX-N-CP-XXXXXXXXXXXXX      3/3/2012    213400      2000000000885230  11886991      2000000000885230 N-2XX-N-KE-XXXXXXX            3/4/2012    170800      1000000000801180  11895835      1000000000801180 ; run; proc sort data=queued; by acct_num tran_dtm; run; proc sort data=worked (rename=workedkey=workedid); by acct_num descending _tran_dtm; run; data _null_ ;   if _n_=1 then do;      set queued (obs=1);       dcl hash q1(dataset:'queued', ordered: 'a');           q1.definekey('acct_num','QueueName');           q1.definedata('acct_num','QueueName','tran_dt','tran_tm','tran_dtm');           q1.definedone();       dcl hash q2(dataset:'queued', multidata:'yes');           q2.definekey('acct_num');           q2.definedata('acct_num','QueueName','tran_dt','tran_tm','tran_dtm');           q2.definedone();       dcl hash q3(multidata: 'yes', ordered: 'a');           q3.definekey('acct_num','tran_dtm','QueueName');           q3.definedata('acct_num','tran_dt','QueueName','tran_tm','WorkedInd', 'WorkedId');           q3.definedone();   end;   do until (last.acct_num);         set worked end=done;         length workedind $7.;         by     acct_num;         retain Inb_dtm inb_workedid pre_workedid;         if _QueueName='Inbound' then             do;                Inb_dtm=_tran_dtm;                Inb_workedid=Workedid;             end;           _rc=q1.find();         if _rc ne 0 then return;         else             do;                  _tran_dt=tran_dt;                   _tran_tm=tran_tm;                  _tran_dtm=tran_dtm;                   pre_workedid=workedid;            end;         do _rc=q2.find() by 0 while (_rc=0);               if  QueueName eq _QueueName and tran_dtm <=_tran_dtm then                   do;                      WorkedInd='Worked';                      workedid=pre_workedid;                      q3.add();                   end;                     else if  QueueName ne _QueueName and tran_dtm <=_tran_dtm then                   do;                      WorkedInd='Removed';                      workedid=pre_workedid;                      q3.add();                   end;                else if Inb_dtm >= tran_dtm > _tran_dtm then                   do;                      WorkedInd='Inbound';                      WorkedId= inb_workedid;                      q3.add();                   end;               else if tran_dtm > inb_dtm then                   do;                      call missing(WorkedInd, workedid);                      q3.add();                   end;               _rc=q2.find_next();         end;   end;   call missing (Inb_dtm, inb_workedid, pre_workedid);   if (done) then q3.output(dataset: 'want'); run; proc print;run; Kindly Regards, Haikuo Update: Oops, has to be SAS 9.2 ... View more