topic Re: If Condition in SAS Programming

If Condition

Timbim — Mon, 22 Oct 2018 09:44:06 GMT

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

Re: If Condition

RW9 — Mon, 22 Oct 2018 10:01:52 GMT

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

I have no idea what you mean by Day5?

Re: If Condition

Timbim — Mon, 22 Oct 2018 10:15:20 GMT

Hi RW9,

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

Thanks kindly.

Re: If Condition

RW9 — Mon, 22 Oct 2018 10:23:28 GMT

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;

Re: If Condition

Timbim — Mon, 22 Oct 2018 10:43:02 GMT

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.

Re: If Condition

RW9 — Mon, 22 Oct 2018 12:12:24 GMT

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-data-AKA-generate/ta-p/258712

Re: If Condition

ballardw — Mon, 22 Oct 2018 15:46:53 GMT

@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.

Re: If Condition

Timbim — Tue, 23 Oct 2018 13:47:02 GMT

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.

Re: If Condition

Timbim — Thu, 25 Oct 2018 03:13:09 GMT

Thanks RW9 for your this logic.

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

Thanks kindly.