Fortnight Data.

[Swati24]

I have a data set for which I have to create fortnight. The report should have 3 columns for example if the date given is 15 Nov,2018 then it should give me the values for col1 from 1 to 15 of Nov,2018, for col2 16 to 31 of Oct,2018 and col3 from 1 to 15 of Nov,2017 for the count of Action.

data set is like this

data one;
input UID Action: $10. Date: Date9.;
datalines;
101 Pending 05Jan2011
102 Executed 09Jan2011
103 Failed 15Feb2011
104 Warning 29Feb2012
105 Executed 1Mar2012
106 Executed 17Mar2013
107 Pending 30Apr2013
108 Failed 18Sep2014
109 Failed 26Nov2014
110 Warning 15Aug2015
111 Pending 31Dec2018
;
run;

[Ksharp]

data one;
input UID Action: $10. Date: Date9.;
format date date9.;
datalines;
101 Pending 05Jan2011
102 Executed 09Jan2011
103 Failed 15Feb2011
104 Warning 29Feb2012
105 Executed 1Mar2012
106 Executed 17Mar2013
107 Pending 30Apr2013
108 Failed 18Sep2014
109 Failed 26Nov2014
110 Warning 15Aug2015
111 Pending 31Dec2018
;
run;
proc sql;
create table want as
select *,
(select count(distinct Action) from one where date between intnx('month',a.date,0) and a.date) as col1,
(select count(distinct Action) from one where date between intnx('month',a.date,-1,'m') 
and intnx('month',a.date,-1,'e')) as col2,
(select count(distinct Action) from one where date between intnx('month',intnx('year',a.date,-1,'s'),0)
and intnx('year',a.date,-1,'s')) as col3
 
 from one as a;
quit;

[Swati24]

In my case I have to get the report only for few restricted dates i.e 15,28,29,30 and 31 for all the years and months. If I use VA to generate the report I can select only these dates.

[ballardw]

Perhaps add a variable representing the first day of the fortnight?

data one;
input UID Action: $10. Date: Date9.;
fortnight = intnx('semimonth',date,0,'b');
format date fortnight date9.;
datalines;
101 Pending 05Jan2011
102 Executed 09Jan2011
103 Failed 15Feb2011
104 Warning 29Feb2012
105 Executed 1Mar2012
106 Executed 17Mar2013
107 Pending 30Apr2013
108 Failed 18Sep2014
109 Failed 26Nov2014
110 Warning 15Aug2015
111 Pending 31Dec2018
;
run;