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!
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;
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.