Attach recent records back to the original

[jiangmi]

data have;

input acct$ month sale;

datalines;

A 20141101 90

A 20141001 88

A 20140901 85

B 20141001 84

B 20140901 80

;

data want;

input acct$ month sale sale_now sale_prior;

datalines;

A 20141101 90 90 88

A 20141001 88 90 88

A 20140901 85 90 88

B 20141001 84 . 84

B 20140901 80 . 84

;

Basically, I want to create two new columns and attach the current month (20141101) sale and the month before (20141001) to the existing records. Right now, I can get my results with some long codes. I believe there are better ways and I want to learn it. Thank

BTW, the current month and the month before will always be different every month. Some acct may not have data on current month (acct B in this case). I am looking for something that works dynamically. 

[stat_sas · Accepted solution]

proc sql;

create table want as

select a.*,b.sale_now,c.sale_prior from have a

left join (select acct,sale as sale_now from have group acct

having month(month)=max(month(month))) b

on a.acct=b.acct

left join

(select acct,sale as sale_prior from have group acct

having month(month)=max(month(month))-1) c

on a.acct=c.acct;

quit;

[jiangmi]

Thanks. This is a better solution to my situation.

[Ksharp]

the current month, you mean the current month in reality (today - Nov)?

 

data have;
input acct$ month : yymmdd10. sale;
format month  yymmdd10.;
datalines;
A 20141101 90
A 20141001 88
A 20140901 85
B 20141001 84
B 20140901 80
;
run;
proc sql;
create table want as
 select *,(select sale from have where acct=a.acct and month=intnx('month',"&sysdate"d,0,'b'))  as sale_now ,
          (select sale from have where acct=a.acct and month=intnx('month',"&sysdate"d,-1,'b'))  as  sale_prior
  from have as a;
quit; 

Xia Keshan

[jiangmi]

Thank you Keshan.

In my case, the current month means the most recent month on book. However, I think your code provide solution to another case scenario. I will save it for later.