Hi everyone,    i try to reproduce the scenario below , any help / idea ?   YEAR id x ord  WANT EXCEL Formula 1 1 0,00218127 1 0,99781873 1-C2 2 1 0,00278531 2 0,9950395 E2*(1-C3) 3 1 0,00519557 3 0,9898697 E3*(1-C4) 3 1 0,00519557 4 0,9898697 E3*(1-C5) 4 1 0,00791179 5 0,98203806 E5*(1-C6) 4 1 0,00791179 6 0,98203806 E5*(1-C7) 1 2 0,00218127 7 0,99781873 1-C8 2 2 0,00278531 8 0,9950395 E8*(1-C9) 3 2 0,00278531 9 0,992268 E9*(1-C10) ... View more

DATA TABLEA; input Name $1. ID $5. smoker_code Year; Cards; A ID1 0 2012 B ID2 1 2010 ; Run; DATA TABLEB; input Name $1. ID $5. want Year; Cards; A ID1 0.5 2010 A ID1 0.6 2011 A ID1 0.7 2012 B ID2 0.8 2008 B ID2 0.8 2009 B ID2 0.8 2010 B ID2 0.9 2011 ; Run; Proc sql; Create table ABC_SQL_full as select A.* ,B.want as want1 ,B.year as Year_past ,C.want as Want2 from (select *,MONOTONIC() as ORD from TABLEA) as a full join TABLEB as b on A.ID=B.ID and A.Name=B.name left join TABLEB as c on A.ID=c.ID and a.Name=c.name and b.year=c.year where b.year le a.year order by ORD,year_past; quit; /* how to perform the same SQL join results with HASH method 1.HASH full join with tableB 2.and HASH left join with same tableB with condition b.year=c.year in the defined key  3.and having the same where and order condition*/   /* here it's an left join in hash that i can use for the second join ... */ data ABC_HASH_full; if 0 then set TABLEB ; set TABLEA ; if _n_=1 then do; declare hash hhh(dataset: "TABLEB", multidata:'y'); hhh.DefineKey('ID','Name'); hhh.DefineData('want'); hhh.DefineDone(); end; if hhh.find(key:ID,Key:Name)=0 then do; output ABC_SQL_full; end; run; ... View more

Dear all,    any idea about how to resolve the following case :    in table a i have the variable "Year" taking value from 2016 to 2022  and in table b Year variable takes value from value 2019 to  2020.   in that case , i would like to apply the following resuls: a.2016=b.2019 ( in table a i would like to take value corresponding to min of year in table b which is 2019 , same case from 2017 to 2019 ) a.2020=b.2020 ( 2020 value from table a exist on table b ) a.2021=b.2020 ( from 2020 to 2021 from table a takes max value from table b which is 2020 ) i've tried the following code , but it doesn't work as expected :   for example that case correspond to the first case a.2016=b.2019 ( in table a i would like to take value corresponding to min of year in table b which is 2019 , same case from 2017 to 2019 )   proc sql; create table want as select a.*,b.var_want from tableA as a left join tableB as b on a.YEAR=max(2019,min(b.YEAR,2020)) ; quit;   it gives an empty value for var_want and doesn't take value of 2019 in table b when year=2016 in table a   thanks for your help ... View more

Dear users,   i would like to replicate the result ( column want ) of my Excel example below .   any help on it i tried many ways , maybe the code below is close to get the right results.     age country region x want 1 FR Europe 0,5 1 2 FR Europe 0,6 0,5 3 FR Europe 0,7 0,2 4 FR Europe 0,8 0,06 5 FR Europe 0,9 0,012 1 US America 0,1 1 2 US America 0,2 0,9 3 US America 0,3 0,72 4 US America 0,4 0,504 5 US America 0,5 0,3024     data want; set have; by country region age; if first.country then y=1; else y=lag(y)*(1-lag(x)); run;       ... View more

SEG 9.4 M5   why do i have the error message below in my log ( i have enough space in the disk associated to the user folder ) :    ERROR: Insufficient space in file SASUSER.PARMS.CATALOG.   is it possible to restore the files to the default values ? ( a reboot of the SAS server ?)  ... View more