Data Difference

Reply
anonymous_user
Posts: 0

Data Difference

Hi, I need help as to how I include within my code the account number that was in last months data (May)  but are not included within the current month (June) only, and present the balance difference between the 2 months by account number. Anyone know how I would go about doing this?

PROC SQL;
create table work.red_balance_may_jul14 as
select

a.accnum,
a.month_date,
a.sum (balance_outstanding) as total_balance

from
gbas.basjul14 as a
inner join gbas.basjun14 as b
on a.acc = b.acc

where


a.platform = 'Opt'
and a.Arr_stage_code not in ('4','5','6')

group by

a.accnum,
a.month_date;
quit;

Super User
Super User
Posts: 7,716

Re: Data Difference

Well, that really depends on your definition of month.  If it is the literal month=jun v month=may then maybe something along the lines of:

proc sql;

     create table WANT as

     select     THIS_MONTH.ACC_NO,

                    THIS_MONTH.BAL - LAST_MONTH.BAL as DIFF

     from        (select * from HAVE where ....) THIS_MONTH

     left join    (select * from HAVE where ...) LAST_MONTH

     on            THIS_MONTH.ACC_NO=LAST_MONTH.ACC_NO

     and          month(THIS_MONTH.DATE) = month(LAST_MONTH.DATE) + 1;

     where      LAST_MONTH.ACC_NO is not null;

quit;

If you have day ranges then you would need other interval functions like INTCK.

anonymous_user
Posts: 0

Re: Data Difference

Sorry im new to this so slightly confused. Where you have THIS_MONTH do i need to replace it with gbas.basjul14?

Im also unsure on the HAVE where.....

Thanks

Super User
Super User
Posts: 7,716

Re: Data Difference

Yes, HAVE is the dataset you already have.  This_month and last_month are alias to the respective subquerys on the rows from and left join.

The main part to take away is the join on month(date) which will give you a number 1-12 for the numeric month.  This then matches to last month's month(date) + 1.  With the where we restrict to data present in both.

anonymous_user
Posts: 0

Re: Data Difference

I can't get it to work, ive tried this below -

proc sql;
create table difference as
select this_month.accnum
this_month.total_balance - last_month.total_balance as diff
from (select * from have where gbasel.basjun14) this_month
left join(select * from have where gbasel.basmay14) last_month
on this_month.accnum = last_night.accnum
and month(this_month.month_date) = month(lastmonth.month_date) + 1
where last_month.accnum is not null;

quit;

Super User
Super User
Posts: 7,716

Re: Data Difference

Theres several typos in that code:

proc sql;
create table difference as
select this_month.accnum
this_month.total_balance - last_month.total_balance as diff
from (select * from have where gbasel.basjun14) this_month   /* The where clause does not do anything */
left join(select * from have where gbasel.basmay14) last_month  /* As above */
on this_month.accnum = last_night.accnum        /* Should be last_month */ 
and month(this_month.month_date) = month(lastmonth.month_date) + 1    /* Missing underscore */
where last_month.accnum is not null;

quit;

There where I put in there to cover these things in your original code:

a.platform = 'Opt'

and a.Arr_stage_code not in ('4','5','6')

anonymous_user
Posts: 0

Re: Data Difference

  I have amended the code and now get an error mesage -

NOTE: PROC SQL can not use index accnum of GBASEL.BASELAPR14 because it has a NOMISS flag

      specified.

NOTE: PROC SQL can not use index accnum of GBASEL.BASELJUN14 because it has a NOMISS flag

      specified.

NOTE: Table WORK.DIFFERENCE created, with 0 rows and 1 columns.

proc sql;

create table difference as

select this_month.accnum - last_month.accnum as diffacc

from (select * from gbasel.baseljun14 where optimum_platform = 'Opt'and arrears_lit_stage_code not in ('4','5','6'))as this_month

left join(select * from gbasel.baselmay14 where optimum_platform = 'Opt'and arrears_lit_stage_code not in ('4','5','6')) as last_month

on this_month.accnum = last_month.accnum

where last_month.accnum is not null;

quit;

Ask a Question
Discussion stats
  • 6 replies
  • 277 views
  • 0 likes
  • 2 in conversation