data have; call streaminit(123); do fy='2022','2023','2024'; do obs=1 to 100; var1=rand('uniform'); if rand('uniform')<0.1 then call missing(var1); var2=rand('uniform'); if rand('uniform')<0.2 then call missing(var2); var3=rand('uniform'); if rand('uniform')<0.3 then call missing(var3); var4=rand('uniform'); if rand('uniform')<0.4 then call missing(var4); var5=rand('uniform'); if rand('uniform')<0.5 then call missing(var5); output; end; end; run; proc sql; create table temp as select fy, nmiss(var1)/(select count(*) from have where fy=a.fy) as var1, nmiss(var2)/(select count(*) from have where fy=a.fy) as var2, nmiss(var3)/(select count(*) from have where fy=a.fy) as var3, nmiss(var4)/(select count(*) from have where fy=a.fy) as var4, nmiss(var5)/(select count(*) from have where fy=a.fy) as var5 from have as a group by fy; quit; proc transpose data=temp out=want prefix=fy; id fy; var var1-var5; run; ... View more

Paige's solution will work then, as long as you have some sort of naming conventions with prefixes. ... View more

data have; infile cards expandtabs truncover; input Account $ Ticker $ Date :mmddyy10. OutstandingShares Price dollar2.; format date mmddyy10. Price dollar2.; cards; X A 01/01/15 100 $1 X A 01/05/15 200 $2 X B 01/01/15 300 $3 X B 01/05/15 600 $6 ; data want; merge have have(keep= Account Ticker Date rename=( Account=A Ticker=T Date=D) firstobs=2); output; if Account = A and Ticker = T then do; do Date=Date+1 to D-1 ; output; end; end; drop A T D; run; ... View more

What you want here is not a lag. It is a lead (look ahead). Due to the construction of the data step processing, it does not support a lead function. However, you can do a workaround like this   data have; input var1 $ var2:mmddyy10.; format var2 mmddyy10.; datalines; A 1/1/14 A 1/2/14 A 1/3/14 A 1/4/14 A 1/5/14 A 1/6/14 A 1/7/14 A 1/8/14 A 1/9/14 A 1/10/14 A 1/11/14 A 1/12/14 ; data want; merge have have(firstobs=2 keep=var2 rename=(var2=var3)); run; ... View more

Since you have a many-to-many relationship, and want a cartesian join, you need to use proc sql. ... View more

Very good idea of using Hash   data a; input airline & $20. country :$10.; cards; Air Asia Vietnam Vietjet Vietnam ; data b; input country $10. city & :$15.; cards; Vietnam Hanoi Vietnam Ho Chi minh ; data want; set a; by country; /*sort not required but improves performance*/ if _n_=1 then do; if 0 then set b; declare hash H (dataset:'b',multidata:'y') ; h.definekey ("country") ; h.definedata ("city") ; h.definedone () ; end; do rc=h.find() by 0 while(rc=0); output; rc=h.find_next(); end; drop rc; run; ... View more

Thanks Chris it worked! ... View more