topic Re: Nested Loop and groupby in SAS Programming

Nested Loop and groupby

dart486945 — Tue, 27 Jul 2021 02:30:59 GMT

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!

Re: Nested Loop and groupby

andreas_lds — Tue, 27 Jul 2021 04:53:21 GMT

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.

Re: Nested Loop and groupby

Ksharp — Tue, 27 Jul 2021 12:14:34 GMT

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;

Re: Nested Loop and groupby

dart486945 — Tue, 27 Jul 2021 20:40:23 GMT

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.

Re: Nested Loop and groupby

Ksharp — Wed, 28 Jul 2021 12:07:29 GMT

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;