Nested Loop and groupby

[dart486945]

Hi all, 

New to loops in SAS and need some help with syntax and logic. My input table looks like the following: 

GROUP	DATE	ACTIVATED
1	1/11/20	1
1	2/11/20	0
1	3/11/20	1
2	1/11/20	0
2	2/11/20	0
3	1/11/20	1
3	2/11/20	1
3	3/11/20	0

and I need to add a flag to identify any inactivation (ACTIVATED=0) if there is at least one activation(ACTIVATED=1) within 35 days. So here is the table result I would like to have: 

GROUP	DATE	ACTIVATED	FLAG
1	1/11/20	1	0
1	2/11/20	0	1
1	3/11/20	1	0
2	1/11/20	0	0
2	2/11/20	0	0
3	1/11/20	1	0
3	2/11/20	1	0
3	3/11/20	0	1

Here is the code I tried that I think the logic was okay, but I'm not familiar with the syntax in SAS. The idea is to have two pointers to go through all pairs in each group.

data want; 
  set input; 
  by GROUP; 
  retain FLAG 0; 
  do i = first.GROUP to last.GROUP; 
    do j = i+1 to last.GROUP; 
      if -35 <= intck('DAY', i.DATE, j.DATE) <= 35 and i.ACTIVATED = 0 and j.ACTIVATED = 1 then i.FLAG=1;
      else if -35 <= intck('DAY', i.DATE, j.DATE) <= 35 and i.ACTIVATED = 1 and j.ACTIVATED = 0 then j.FLAG=1;
      end; 
   end; 
run; 

Any inputs would be appreciated! 

[andreas_lds]

If you are new to sas, you should read the documentation or attend to the 1st programming course to get a basic understanding of the fundamental concepts of sas.

A data step is always a loop:

 69         data work.ClassClone;
 70            set sashelp.class;
 71         
 72            put Name=;
 73         run;
 
 Name=Alfred
 Name=Alice
 Name=Barbara
 Name=Carol
 Name=Henry
 Name=James
 Name=Jane
 Name=Janet
 Name=Jeffrey
 Name=John
 Name=Joyce
 Name=Judy
 Name=Louise
 Name=Mary
 Name=Philip
 Name=Robert
 Name=Ronald
 Name=Thomas
 Name=William
 NOTE: There were 19 observations read from the data set SASHELP.CLASS.
 NOTE: The data set WORK.CLASSCLONE has 19 observations and 5 variables.

Back to your task: why is flag=1 in the second observation? Also please post data in usable form, especially when data contains dates, we need to know that the dates are sas dates and which format is attached, i use dd/mm/yyyy normally, but your dates could be in the weird mm/dd/yy format.

[Ksharp · Accepted solution]

Assuming I understand your question.

data have;
infile cards expandtabs truncover;
input GROUP	DATE : ddmmyy10.	ACTIVATED;
format date ddmmyy10.;
cards;
1	1/11/20	1
1	2/11/20	0
1	3/11/20	1
2	1/11/20	0
2	2/11/20	0
3	1/11/20	1
3	2/11/20	1
3	3/11/20	0
;

proc sql;
create table want as
select *,(ACTIVATED=0 and sum(ACTIVATED=1) and range(date)<=35) as flag
 from have
  group by group
   order by 1,2;
quit;

[dart486945]

Thanks Ksharp for bringing up the sql version solution. This is close to what I was looking for, except for the range(date) looking at max date and min date instead of sequential dates. I think I know a solution to proceed.

I still think the nested loop solution would be more straightforward here to have two pointers and look at every pair within each group. Would be great to learn how to do it in SAS.

[Ksharp]

OK. You could try DOW skill. Also assuming your data has been sorted by group,date ;

data have;
infile cards expandtabs truncover;
input GROUP	DATE : ddmmyy10.	ACTIVATED;
format date ddmmyy10.;
cards;
1	1/11/20	1
1	2/11/20	0
1	3/11/20	1
2	1/11/20	0
2	2/11/20	0
3	1/11/20	1
3	2/11/20	1
3	3/11/20	0
;
data want;
 do until(last.group);
  set have;
  by group;
  if first.group then first_date=date;
  if ACTIVATED=1 then has_ACTIVATED=1;
 end;
range_date=date-first_date;
 do until(last.group);
  set have;
  by group;
  if ACTIVATED=0 and has_ACTIVATED and range_date<=35 then flag=1;
   else flag=0;
   output;
 end;
 drop has_ACTIVATED range_date first_date;
run;