Dear All:
I have flags for certain dates. I want to construct two variables, for VarA and VarB, called VarA_BF and VarB_BF, that are lagged two days from the date of the flag and two days forward from the flag date.
Thanx in advance
My Data is as Follows
Date VarA VarB VarA_BF VarB_BF
06/20/2019 1
06/20/2019 1
06/20/2019 1
06/20/2019 1
06/21/2019 1 1
06/21/2019 1 1
06/21/2019 1 1
06/22/2019 1 1 1
06/22/2019 1 1 1
06/22/2019 1 1 1
06/22/2019 1 1 1
06/23/2019 1 1 1
06/23/2019 1 1 1
06/23/2019 1 1 1
06/24/2019 1 1
06/24/2019 1 1
06/24/2019 1 1
06/24/2019 1 1
06/26/2019 1
06/26/2019 1
06/27/2019
06/27/2019
06/27/2019
06/28/2019
Since there is a LAG function in SAS but no LEAD function, you use LAG2() to obtain results from two days in the past, then sort the data set so that dates are descending, and use LAG2() to obtain results from two days in the future.
Are your dates continuous or not?
25th is missing, otherwise it seems continuous???
@novinosrin wrote:
Are your dates continuous or not?
25th is missing, otherwise it seems continuous???
Good question, also most (all?) dates have multiple records, it's not clear how that should be handled.
The dates are NOT continuous. Sorry for not making it clear.
Using the Lag function does not work, as the dates are repeated in the data set.
Thanx so much.
@novinosrin wrote:Are your dates continuous or not?
25th is missing, otherwise it seems continuous???
@RandyStan wrote:
The dates are NOT continuous. Sorry for not making it clear.
Using the Lag function does not work, as the dates are repeated in the data set.
Thanx so much.
Then clearly my approach will not work. 😞
Okay @RandyStan Can you please post the sample that I could easily copy paste. i.e border the cells, for missing ones put .(dot) and paste it back here. Thank you!
Here goes.
Thanx.
Date | VarA | VarB | VarA_BF | VarB_BF |
6/20/2019 | 1 | |||
6/20/2019 | 1 | |||
6/20/2019 | 1 | |||
6/20/2019 | 1 | |||
6/21/2019 | 1 | 1 | ||
6/21/2019 | 1 | 1 | ||
6/21/2019 | 1 | 1 | ||
6/22/2019 | 1 | 1 | 1 | |
6/22/2019 | 1 | 1 | 1 | |
6/22/2019 | 1 | 1 | 1 | |
6/22/2019 | 1 | 1 | 1 | |
6/23/2019 | 1 | 1 | 1 | |
6/23/2019 | 1 | 1 | 1 | |
6/23/2019 | 1 | 1 | 1 | |
6/24/2019 | 1 | 1 | ||
6/24/2019 | 1 | 1 | ||
6/24/2019 | 1 | 1 | ||
6/24/2019 | 1 | 1 | ||
6/26/2019 | 1 | |||
6/26/2019 | 1 | |||
6/27/2019 | ||||
6/27/2019 | ||||
6/27/2019 | ||||
6/28/2019 |
Just added 0's to the table
Date | VarA | VarB | VarA_BF | VarB_BF |
6/20/2019 | 0 | 0 | 0 | 1 |
6/20/2019 | 0 | 0 | 0 | 1 |
6/20/2019 | 0 | 0 | 0 | 1 |
6/20/2019 | 0 | 0 | 0 | 1 |
6/21/2019 | 0 | 0 | 1 | 1 |
6/21/2019 | 0 | 0 | 1 | 1 |
6/21/2019 | 0 | 0 | 1 | 1 |
6/22/2019 | 0 | 1 | 1 | 1 |
6/22/2019 | 0 | 1 | 1 | 1 |
6/22/2019 | 0 | 1 | 1 | 1 |
6/22/2019 | 0 | 1 | 1 | 1 |
6/23/2019 | 1 | 0 | 1 | 1 |
6/23/2019 | 1 | 0 | 1 | 1 |
6/23/2019 | 1 | 0 | 1 | 1 |
6/24/2019 | 0 | 0 | 1 | 1 |
6/24/2019 | 0 | 0 | 1 | 1 |
6/24/2019 | 0 | 0 | 1 | 1 |
6/24/2019 | 0 | 0 | 1 | 1 |
6/26/2019 | 0 | 0 | 1 | 0 |
6/26/2019 | 0 | 0 | 1 | 0 |
6/27/2019 | 0 | 0 | 0 | 0 |
6/27/2019 | 0 | 0 | 0 | 0 |
6/27/2019 | 0 | 0 | 0 | 0 |
6/28/2019 | 0 | 0 | 0 | 0 |
Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
Hello @RandyStan Thank you.
See if this works and helps. Feeling not too well today and so might require a minor tweak. Please test and let me know. Thank you!
data have;
input Date :mmddyy10. VarA VarB;
format date mmddyy10.;
cards;
6/20/2019 . .
6/20/2019 . .
6/20/2019 . .
6/20/2019 . .
6/21/2019 . .
6/21/2019 . .
6/21/2019 . .
6/22/2019 . 1
6/22/2019 . 1
6/22/2019 . 1
6/22/2019 . 1
6/23/2019 1 .
6/23/2019 1 .
6/23/2019 1 .
6/24/2019 . .
6/24/2019 . .
6/24/2019 . .
6/24/2019 . .
6/26/2019 . .
6/26/2019 . .
6/27/2019 . .
6/27/2019 . .
6/27/2019 . .
6/28/2019 . .
;
dm log 'clear';
data want;
dcl hash H () ;
h.definekey ("_iorc_") ;
h.definedata ("_date") ;
h.definedone () ;
do until(z);
set have end=z;
by date;
if first.date then do;
_iorc_+1;
_date=date;
rc=h.add();
end;
end;
z=0;
_iorc_=0;
do until(z);
set have end=z;
by date;
array u VarA VarB;
array v VarA_BF VarB_BF;
array t(2,999999) _temporary_;
if first.date then do;
_iorc_+1;
do i=1 to dim(u);
if u(i)=1 then do;
do _n_=_iorc_-2 to _iorc_+2;
rc=h.find(key:_n_);
if rc=0 then t(i,_n_)=_date;
end;
end;
end;
end;
end;
z=0;
do until(z);
set have end=z;
by date;
if first.date then do;
call missing(of v(*));
do i=1,2;
do j=1 to 999999;
if t(i,j)=date then do;
v(i)=1;
leave;
end;
end;
end;
end;
output;
end;
stop;
keep date var:;
run;
Do you want to carry forward two DAYS or two distinct dates? In your example the A flag is being carried forward 3 days, from 23rd to 26th.
Since there is no 06/25/2019 the flag should go to 06/26/2019.
As I have noted earlier the date series is not continuous.
Do the multiple observations for the same date always have the same values for the two flag variables?
If not then which value is to be used to create the new variables?
Tom: I do not follow your question
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.