DATA Step, Macro, Functions and more

Attach recent records back to the original

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

Attach recent records back to the original

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. 


Accepted Solutions
Solution
‎11-14-2014 03:48 PM
Trusted Advisor
Posts: 1,204

Re: Attach recent records back to the original

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;

View solution in original post


All Replies
Solution
‎11-14-2014 03:48 PM
Trusted Advisor
Posts: 1,204

Re: Attach recent records back to the original

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;

Contributor
Posts: 61

Re: Attach recent records back to the original

Thanks. This is a better solution to my situation.

Super User
Posts: 9,687

Re: Attach recent records back to the original

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

Contributor
Posts: 61

Re: Attach recent records back to the original

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 207 views
  • 3 likes
  • 3 in conversation