I am trying to select the most recent information on a Member and Plan basis, but the payments can be adjusted in future reports, and I would like to select those values. The data is coming from a SQL database, but I have provided sample data here: proc sql;
create table HAVE
(Member char(10),
PlanType char(3),
ReportDate num informat=yymmn6.
format=yymmn6.,
StartDate num informat=yymmdd10.
format=yymmdd10.,
EndDate num informat=yymmdd10.
format=yymmdd10.,
AdjustmentReason char(64), Code char(1),
Payment num);
insert into HAVE
values('123AAABBBC', '001', '01JAN24'd, '01JAN24'd, '31JAN24'd, '', 'A', 500)
values('123AAABBBC', '001', '01FEB24'd, '01FEB24'd, '29FEB24'd, '', 'A', 500)
values('123AAABBBC', '001', '01MAR24'd, '01MAR24'd, '31MAR24'd, '', 'A', 500)
values('123AAABBBC', '001', '01APR24'd, '01APR24'd, '30APR24'd, '', 'A', 500)
values('123AAABBBC', '001', '01MAY24'd, '01APR24'd, '30APR24'd, 'Retro Disenroll', 'A', 500)
values('123AAABBBC', '002', '01MAY24'd, '01APR24'd, '30APR24'd, 'Retro Enroll', 'A', 500)
values('123AAABBBC', '002', '01MAY24'd, '01MAY24'd, '31MAY24'd, '', 'A', 500)
values('123AAABBBC', '001', '01JUN24'd, '01JUN24'd, '30JUN24'd, '', 'A', 500)
values('123AAABBBC', '001', '01JUN24'd, '01JAN24'd, '31MAR24'd, 'Retro Adjustment', 'B', 450)
values('123AAABBBC', '001', '01JUN24'd, '01JUN24'd, '30JUN24'd, 'Retro Adjustment', 'B', 650);
select *
from HAVE;
quit;
run; I have tried to create dummy variables by month and ordered them, so that the most recent value is the closest to the top of the table, but I am struggling to actually select the value I want. PaymentJan2024 = 0; PaymentFeb2024 = 0; PaymentMar2024 = 0; PaymentApr2024 = 0; PaymentMay2024 = 0; PaymentJun2024 = 0;
length CodeJan2024 CodeFeb2024 CodeMar2024 CodeApr2024 CodeMay2024 CodeJun2024 $1.;
if StartDate <= '01JAN2024'd < EndDate then do; PaymentJan2024 = Payment; CodeJan2024 = Code; end;
if StartDate <= '01FEB2024'd < EndDate then do; PaymentFeb2024 = Payment; CodeFeb2024 = Code; end;
if StartDate <= '01MAR2024'd < EndDate then do; PaymentMar2024 = Payment; CodeMar2024 = Code; end;
if StartDate <= '01APR2024'd < EndDate then do; PaymentApr2024 = Payment; CodeApr2024 = Code; end;
if StartDate <= '01MAY2024'd < EndDate then do; PaymentMay2024 = Payment; CodeMay2024 = Code; end;
if StartDate <= '01JUN2024'd < EndDate then do; PaymentJun2024 = Payment; CodeJun2024 = Code; end; I believe the end result should look something like the following example, though I am open to feedback and adjustments to make the results understandable. I use '' to indicate no value. Member | Plan | PaymentJan2024 | CodeJan2024 | PaymentFeb2024 | CodeFeb2024 | PaymentMar2024 | CodeMar2024 | PaymentApr2024 | CodeApr2024 | PaymentMay2024 | CodeMay2024 | PaymentJun2024 | CodeJun2024 | TotalPayment2024 123AAABBBC | 001 | 450 | B | 450 | B | 450 | B | 0 | '' | 0 | '' | 650 | B | 2000 123AAABBBC | 002 | 0 | '' | 0 | '' | 0 | '' | 500 | A | 500 | A | 0 | '' | 1000
... View more