If Condition

[Timbim]

Hi Community,

Could you please help me with the following.

Below is a test data table and the output I am looking for meets the following conditions:

1. Give me the list of accounts that contains the 'AccBal' if 'Date' equals OpenDate and also

2. For those accounts that meet the above condtion give me the 'O_Bal' for Day5

Date	AccNo	AccBal	O_Bal	OpenDate
1-Sep-18	123456	10,000.00	2000	1-Sep-18
2-Sep-18	123456	10,000.00	2000	1-Sep-18
3-Sep-18	123456	10,000.00	2000	1-Sep-18
4-Sep-18	123456	10,000.00	2000	1-Sep-18
5-Sep-18	123456	10,000.00	2000	1-Sep-18
6-Sep-18	123456	10,000.00	2000	1-Sep-18
7-Sep-18	123456	10,000.00	2000	1-Sep-18
8-Sep-18	123456	10,000.00	2000	1-Sep-18
9-Sep-18	123456	10,000.00	2000	1-Sep-18
10-Sep-18	123456	10,000.00	2000	1-Sep-18
5-Sep-18	123456	15,000.00	3000	5-Sep-18
6-Sep-18	123456	15,000.00	3000	5-Sep-18
7-Sep-18	123456	15,000.00	3000	5-Sep-18
8-Sep-18	123456	15,000.00	3000	5-Sep-18
9-Sep-18	123456	15,000.00	3000	5-Sep-18
10-Sep-18	123456	15,000.00	3000	5-Sep-18

Thanks kindly

[RW9]

1) 

proc sort data=have out=want nodupkey;
  by accno accbal;
  where date=opendate;
run;

2)

I have no idea what you mean by Day5?

[Timbim]

Hi RW9,

 Day5 meaning the O_Bal on the 5th Calender Day using the 'Date' field.

Thanks kindly.

[RW9 · Accepted solution]

If thats 5 days from the date then something like:

proc sort data=have out=inter nodupkey;
  by accno accbal;
  where date=opendate;
run;

proc sql;
 create table want as
 select a.*,
        b.accbal as day5
 from inter a
 left join have b
 on     a.accno=b.accno
 and    b.date=a.date+5;
quit;

[Timbim]

Hi RW9

Thanks for your prompt response.

The first logic works but the 2nd gives me blank for Day 5. It is the O_Bal that I want at Day5 and not the AccBal. I replaced AccBal as Day5 with O_Bal at Day5 using your logic but getting blanks.

Thanks kindly.

[RW9]

Post some test data in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[ballardw]

---

@Timbim wrote:

Hi RW9

Thanks for your prompt response.

The first logic works but the 2nd gives me blank for Day 5. It is the O_Bal that I want at Day5 and not the AccBal. I replaced AccBal as Day5 with O_Bal at Day5 using your logic but getting blanks.

Thanks kindly.

---

Show the code you ran and the log.

Are your dates actual SAS date values or character values masquerading as dates? If so, then the +5 would likely throw an error of some sort.

[Timbim]

Hi Ballard,

My sincere apologies. The logic posted by RW9 works however I have about 2 records that giving me blank dates ( with a period sign) instead of the dates. I checked the raw data and it does have dates for those 2 records.

My logic is as follows

Data bal_startdate (rename=(Date=StartDate) drop=Offset_Bal);

set Daily;

by AccNo;

if first.AccNo;

run;

Proc Sql;

Create table Day5_bal as

select a.*,

b.offset_Bal,

b.date as Day5,

case

when b.date is missing then 'N' else 'Y' end as Day5_Flag

From bal_startdate a

left join Daily b on a.AccNo=b.AccNo and b.date=a.StartDate+5;

quit;

The results are as I want however I've picked 2 accounts that having missing 'b.date as Day 5' when there is a date in the raw data.

Thanks for your help.

[Timbim]

Thanks RW9 for your this logic.

I revisited my data and tried your logic again and it works.

Thanks kindly.