Dear Shivas, this is the log 301  proc sql; 302 303  create table vwretd.adv_vwretd5 as 304 305  select a.* , b.* 306 307  from vwretd.sorted_vwretd_pexpand a left join  vwretd.sortcompustat b 308 309  on a.permno=b.lpermno and a.fyear=b.fyear 310 311  order by a.permno, a.fyear; WARNING: Variable FYEAR already exists on file VWRETD.ADV_VWRETD5. NOTE: Table VWRETD.ADV_VWRETD5 created, with 53539 rows and 45 columns. 312 313  quit; is the problem considered solved? ... View more

Dear Ksharp, only variable of fyear of b (vwreted.sortcompustat) would be included in the table, other variables required would not be selected. ... View more

This is the log 197  proc sql; 198  create table vwretd.adv_vwretd3 as 199  select a.* , b.* 200  from vwretd.sorted_vwretd_pexpand a left join  vwretd.sortcompustat b 201  on a.permno=b.lpermno and a.fyear=b.fyear 202  order by permno, fyear; WARNING: Column named fyear is duplicated in a select expression (or a view). Explicit          references to it will be to the first one. WARNING: Variable FYEAR already exists on file VWRETD.ADV_VWRETD3. NOTE: Table VWRETD.ADV_VWRETD3 created, with 53539 rows and 45 columns. Is there anyway i can improve to address the warning? ... View more

To avoid "lag" function to give me the "lag value" from last company if you don't put any restriction, I have inserted this: " if permno ne lag1(permno) then lag1_idiorisk = .; if permno ne lag2(permno) then lag2_idiorisk = .; if permno ne lag3(permno) then lag3_idiorisk = .; if permno ne lag4(permno) then lag4_idiorisk = .; run;  " so now the program read as data vwretd.sorted_main_vwretd_test;    set vwretd.sorted_main_vwretd_test nobs=_nobs_;    lag1_idiorisk = lag1(idiorisk);    lag2_idiorisk = lag2(idiorisk);    lag3_idiorisk = lag3(idiorisk);    lag4_idiorisk = lag4(idiorisk); if permno ne lag1(permno) then lag1_idiorisk = .; if permno ne lag2(permno) then lag2_idiorisk = .; if permno ne lag3(permno) then lag3_idiorisk = .; if permno ne lag4(permno) then lag4_idiorisk = .; run; if _n_ < _nobs_ then set vwretd.sorted_main_test (keep=idiorisk rename=(idiorisk=fwd1_idiorisk) firstobs=2);    if _n_ < _nobs_-1 then set vwretd.sorted_main_test (keep=idiorisk rename=(idiorisk=fwd2_idiorisk) firstobs=3); run; Question: I m not sure how to put the restrictions on the forward value to restrict the company pick the forward value from the previous permno. Pls help. ... View more

how do i modify my program? ... View more

how proc expand works for lead and lags? can you show me the programming ? What is ETS? ... View more

thanks!!!it works !! ... View more

Thanks Ksharp， my stupidity to run the first part of data step and corrupt the data...i don't need to do it as i already have the datasets. running from the pro sql give me the answer!! Thanks mei ... View more

Tom wrote: If the set of variables are the same then you can do it by referencing the original dataset first so that SAS "sees" the variables in that order first. Use OBS=0 dataset option to prevent SAS from actually reading any observations from that source. data fixed ;    set original(obs=0) have ; run; hi Tom, just clarify, is this run first before running kSharp program? ... View more

hi Tom, Forgive me if i ask stupid q, as i m not very familiar with sql.. my question is do i replace the ' d b c a ' with variables in my file? if yes, then i have to type all 58 variables? options user=work; data origin ; input d b c a; cards; 1 2 3 4 ; run; data output; input a b c d; cards; 4 2 3 1 ; run; ... View more