Help: using SAS Macro to create complex data set

[NIUNIU]

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;

[Reeza]

Questions first:
1. Do you have to have a macro or will a datastep or Proc SQL work?
2. How are you defining hit exactly? maybe I'm missing something but it doesn't look like 48 hours between ids in the sample data set.

[NIUNIU]

Hi,

Thank you very much for your reply.

1) Since I will have many other different variables with the same transformation, so it would be great to have a macro to run them all. But datastep or Proc SQL would be OK .



(2)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.


Thanks again. Message was edited by: NIU_YA

[Reeza]

Ideally I think this should be done via Hash tables if the data can fit in memory.

The method that comes to mind for me is a sql join on itself.

I'm pretty sure the code won't work below but its the idea. This questions has been asked around health care data on this site so some googling might help find a closer solution.

ie proc sql;
create table test as
select a.*, (select count(*) from have b where a.account=b.account and a.id=b.id and a.date-b.date<2) as hit
from have a;
quit;

[NIUNIU]

Thank you for the inputs.

Cheers,

[Ksharp]

[pre]
data temp;
input account $ date : mmddyy10. time : time9. id ;
format date mmddyy10. time time9.;
datalines;
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
;
run;
proc sql;
create table want(drop=count) as
select distinct a.* ,count(distinct b.date) as count ,case
when(calculated count =1) then 0
else 1
end as hit
from temp as a, temp as b
where a.account=b.account and b.date between a.date-2 and a.date
group by a.account,a.date,a.id
;
quit;
proc sort data=want ;
by decending date;
run;
[/pre]


Ksharp

[NIUNIU]

Thank you very much for your inputs.

After i tried in the real data, I found the code should work after a slightly adjustment as below.

select count(distinct b.id) as count

It should not distinct on date. Let me know if my logic is wrong.

Thanks again.

Message was edited by: NIU_YA Message was edited by: NIU_YA

[Reeza]

According to the logic you provided it should be a 1.

ie same account number and same id and the dates are less than 2 days apart.

Is there some other logic?

[Ksharp]

Yes.You are right.
It should be b.id.
It is typo mistake.Thank you point it out for me.


Ksharp

[Patrick]

Hi

I believe what Ksharp's code - with the little glitch -nicely demonstrates is that before you try and solve something with a macro you first get the code right for one of your cases - and only then generalise it via a macro.

Also: If performance should become an issue AND the source tables are in SAS then a "hash approach" would perform better - but the code would also become quite a bit more complicated.

Cheers
Patrick Message was edited by: Patrick

[NIUNIU]

Yes, I think you make the point.

Thank you.

[NIUNIU]

Thank you for the help.