Using logic in meta data to extract payment data
inspired by
https://goo.gl/orP6AA
https://communities.sas.com/t5/Base-SAS-Programming/How-do-a-I-use-an-array-or-macro-to-replace-my-repeating-code/m-p/343776
You need to cycle through logic involving four arrays
using date ranges and constants for every record
in the paid dataset.
This can be solved using SQL quite easily but I don't think
that was the solution the op wanted.
HASH may also be possible, but the logic in the
mata data can change.
HAVE Two datasets meta and paid datasets
====
HAV1ST with valid data data combinations of DTC and DTW)
HAV2ND data to be checked against meta data and
This defines the combinations(meta data) of valid we want to check against
Up to 40 obs WORK.HAV1ST total obs=16
HAV1ST
Obs PWRKCODE WORKCODE DTC DTW
1 D P JAN2016 JAN2016 * matches a range in data
2 D P JAN2016 MAY2016
3 D P JAN2016 JUL2016
4 D P JAN2016 AUG2016
5 D P MAY2016 JAN2016
6 D P MAY2016 MAY2016 * matches
7 D P MAY2016 JUL2016
8 D P MAY2016 AUG2016
9 D P JUL2016 JAN2016
10 D P JUL2016 MAY2016
11 D P JUL2016 JUL2016 *
12 D P JUL2016 AUG2016
13 D P AUG2016 JAN2016 * no match
14 D P AUG2016 MAY2016 * no match
15 D P AUG2016 JUL2016 * no match
16 D P AUG2016 AUG2016 * no match
HAV22ND (This is the main dataset)
Obs PWRKCODE WORKCODE DTCLPAID DTWORK
1 D P 15JAN2016 15JAN2016 * this is valid (in HAV1ST month January)
2 D P 15JAN2016 22JAN2016 * this is valid (in HAV1ST)
3 X P 15MAR2016 15MAR2016 * invalid not in HAV1ST
4 X P 15MAR2016 22MAR2016 * invalid not in HAV1ST
5 D P 15MAY2016 15MAY2016 * this is valid (in HAV1ST)
6 D P 15MAY2016 22MAY2016 * this is valid (in HAV1ST)
7 D P 15JUL2016 15JUL2016
8 D P 15JUL2016 22JUL2016
WANT (Months where customers paid)
====
Macro variable
Month_paid = MONTHPAID=JAN2016 MAY2016 JUL2016
DETAILS
You need to cycle through logic involving four arrays
using date ranges and constants for every record
in the paid dataset.
Note I only used 31 day months to simplify the problem.
if pwrkcode=pwr[mmyy]
and workcode=wrk[mmyy]
and (dtclower[mmyy] <= dtclpaid <=dtcupper[mmyy])
and (dtwlower[mmyy] <= dtwork <=dtwupper[mmyy])
then Month=dtc[mmyy];
* _ _ _
_ __ ___ __ _| | _____ __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| | < __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|
;
proc datasets lib=work kill;
run;quit;
data hav1st;
retain pwrkcode 'D' workcode 'P';
do dtc = 'JAN2016','MAY2016','JUL2016','AUG2016';
do dtw = 'JAN2016','MAY2016','JUL2016','AUG2016';
output;
end;
end;
run;quit;
data hav2nd;
retain pwrkcode 'D' workcode 'P' dtclpaid '01jan2016'd dtwork '31jan2016'd;
format dtclpaid dtwork date9.;
do dtclpaid = '15jan2016'd,'15mar2016'd,'15may2016'd,'15jul2016'd;
if dtclpaid = '15mar2016'd then pwrkcode='X';else pwrkcode='D';
dtwork = dtclpaid;
output;
dtwork = dtwork + 7;
output;
end;
run;quit;
* _ _ _
___ ___ | |_ _| |_(_) ___ _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|
;
%symdel dtc dtw pwr wrk monthpaid;
data _null_;
* note it is good idea to do the sql code
here because macro variable sqlobs is very volatile;
* create data for logic arrays;
if _n_=0 then do;
%sysfunc(dosubl('
proc sql;
select
quote(min(dtc))
,quote(min(dtw))
,quote(min(pwrkcode))
,quote(min(workcode))
into
:dtc separated by ','
,:dtw separated by ','
,:pwr separated by ','
,:wrk separated by ','
from
hav1st
group
by dtc
,dtw
,pwrkcode
,workcode
;quit;
'));
end;
length monthpaid $1000;
retain monthpaid ' ';
array dtc[&sqlobs] $7 (&dtc.) ;
array dtw[&sqlobs] $7 (&dtw.) ;
array pwr[&sqlobs] $7 (&pwr.) ;
array wrk[&sqlobs] $7 (&wrk.) ;
set hav2nd end=dne;
format _all_;
do mmyy=1 to &sqlobs;
dtc_1=input(cats("01",dtc[mmyy]),date9.);
dtc_2=input(cats("31",dtc[mmyy]),date9.);
dtw_1=input(cats("01",dtw[mmyy]),date9.);
dtw_2=input(cats("31",dtw[mmyy]),date9.);
if pwrkcode=pwr[mmyy]
and workcode=wrk[mmyy]
and (dtc_1 <= dtclpaid <=dtc_2)
and (dtw_1 <= dtwork <=dtw_2)
then Month=dtc[mmyy];
if month ne '' then leave;
end;
if indexw(monthpaid,month)=0 then monthpaid=catx(' ',monthpaid,month);
if dne then call symputx('monthpaid',monthpaid);
run;quit;
%put &=monthpaid;
MONTHPAID=JAN2016 MAY2016 JUL2016
... View more
