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.
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;
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;
Thanks. This is a better solution to my situation.
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
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.