×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help: using SAS Macro to create complex data set

Help: using SAS Macro to create complex data set

Help: using SAS Macro to create complex data set

(OP)
I have a data set similar to the following simple data set. With the four column of Account, Date, Time and ID, I like to using SAS macro to create a column like "HIT" with 0 and 1 value. The logic is " there is more than 1 of ID used within 2 days from the same account." If the logic is satisfied, then the variable HIT is 1, else is 0.



Account    date              Time             ID    hit
387000    6/10/2010    14:00:00    13.15    1
387000    6/9/2010    13:00:00    13.15    0
387000    6/8/2010    12:00:00    13.14    1
387000    6/6/2010    11:00:00    13.15    1
387000    6/5/2010    10:00:00    13.14    1
387000    6/4/2010    8:00:00    13.13    0
387000    6/4/2010    9:00:00    13.13    0
386000    5/9/2010    10:00:00    12.12    0
386000    5/2/2010    9:00:00    12.14    1
386000    5/1/2010    8:00:00    12.13    0
385000    3/3/2010    10:00:00    11.13    1
385000    3/1/2010    8:00:00    11.12    0
385000    3/1/2010    9:00:00    11.12    0



Below is my logic trying to create this variable. But I cannot continue anymore. I would really appreciate someone can help to instruct me out of this puzzle. Thank you very much!


proc sort data=file; by account decending date decending time;run;

%macro test;

proc sql noprint;
select count(distinct account)
into :num_cin
from file;
quit;
%let num_cin=&num_cin;
%put num_cin=&num_cin;

proc sql noprint;
select distinct account
into :CIN1-:CIN&num_cin
from file;
quit;
%put CIN1-:CIN&num_cin ;

**assign date **;
%do i=1 %to &num_cin;

proc sql noprint;
    select count(date)
        into :num_date
        from file
        where account=&&cin&i
        ;
quit;
%let num_date=&num_date;
%put num_date=&num_date &&cin&i;

proc sql noprint;
    select date
        into :dat1-:dat&num_date
        from file a
        where account=&&cin&i
        ;
quit;


%end;
%mend test;
%test;
 

RE: Help: using SAS Macro to create complex data set

(OP)
I tried to revised the above example to make it clear.

Account date Time ID hit
387000 6/10/2010 12:00:00 13.15 1
387000 6/9/2010 12:00:00 13.15 1
387000 6/8/2010 12:00:00 13.14 1
387000 6/6/2010 11:00:00 13.15 1
387000 6/5/2010 10:00:00 13.14 1
387000 6/4/2010 9:00:00 13.13 0
387000 6/4/2010 8:00:00 13.13 0
386000 5/9/2010 10:00:00 12.12 0
386000 5/2/2010 9:00:00 12.14 1
386000 5/1/2010 8:00:00 12.13 0
385000 3/3/2010 10:00:00 11.13 1
385000 3/1/2010 8:00:00 11.12 0
385000 3/1/2010 9:00:00 11.12 0

For example,
(1) For account 387000, since on 06/10/2010, the ID used is 13.15, it is different than the ID of 13.14 used on 06/08/2010 (which is within two days of 06/10/2010), so the hit is 1.

(2) For Account 387000, since on 06/09/2010, the ID used is 13.15, it is different than the ID of 13.14 used on 06/08/2010(which is within two days of 06/09/2010), so the hit is 1.

(3)For Account 387000, since on 06/04/2010 at 9:00am, the ID used is 13.13, within 2 days, the only date is another ID of same 13.13 on 06/04/2010 at time of 8:00am. The hit is 0.

(4)For account 386000, within 2 days of 05/09/2010 would be 05/08/2010 or 05/07/2010. But there is no date likes these two, so the hit is 0.

 

RE: Help: using SAS Macro to create complex data set

Not something that I would try to solve with Macro, but definately better handled with the datastep or SQL.

Here is an approach that uses a cartesian product (compare every record with every other record), and then whittles the list down based on the criteria you mentioned above. We get some discrepancies on which records we have a hit, mine are called x:

CODE

data have(drop =_:) ;
   input Account (_date _Time)(:$10.) ID hit ;
   date = input(_date, anydtdte.) ;
   time = input(_time, anydttme.) ;
   dttime = dhms(date,0,0,time) ;
   format date date9. time time5. dttime datetime20. ;
cards ;
387000 6/10/2010 12:00:00 13.15 1
387000 6/9/2010 12:00:00 13.15 1
387000 6/8/2010 12:00:00 13.14 1
387000 6/6/2010 11:00:00 13.15 1
387000 6/5/2010 10:00:00 13.14 1
387000 6/4/2010 9:00:00 13.13 0
387000 6/4/2010 8:00:00 13.13 0
386000 5/9/2010 10:00:00 12.12 0
386000 5/2/2010 9:00:00 12.14 1
386000 5/1/2010 8:00:00 12.13 0
385000 3/3/2010 10:00:00 11.13 1
385000 3/1/2010 8:00:00 11.12 0
385000 3/1/2010 9:00:00 11.12 0
;;;
proc sql ;
create table want(drop=n dttime) as
   select h1.*, monotonic() as n, (h2.id ne h1.id and h2.id is not null) as x
   from have h1 left join have h2
   on 0<(abs(h1.dttime-h2.dttime)/(60*60*24))<= 2
   and h1.account = h2.account
   group by h1.account, h1.dttime, h1.id
   having max(x) =x and min(n) =n;
quit;
proc print; run;

output

CODE

 Obs    Account      ID     hit         date     time    x

 1     385000    11.12     0     01MAR2010     8:00    0
 2     385000    11.12     0     01MAR2010     9:00    0
 3     385000    11.13     1     03MAR2010    10:00    0
 4     386000    12.13     0     01MAY2010     8:00    1
 5     386000    12.14     1     02MAY2010     9:00    1
 6     386000    12.12     0     09MAY2010    10:00    0
 7     387000    13.14     1     05JUN2010    10:00    1
 8     387000    13.15     1     06JUN2010    11:00    1
 9     387000    13.14     1     08JUN2010    12:00    1
10     387000    13.15     1     09JUN2010    12:00    1
11     387000    13.15     1     10JUN2010    12:00    1

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close