Ok. Thanks Joe.

On Mar 2, 3:50 am, snoopy...@GMAIL.COM (Joe Matise) wrote:

> A macro is NOT a function, despite it appearing somewhat like one. It's > just replacement text, albeit text you can do some fun things with. So you > can only do > var =3D %macro(stuff); > when %macro(stuff) resolves directly to a single value. You cannot do > > %macro stuff; > proc sql; > select count(1) from sashelp.class; > quit; > %mend stuff; > data test; > var =3D %stuff; > run; > > because that's the same as saying: > > data test; > var =3D proc sql; select count(1) from sashelp.class; quit;; > run; > > which quite clearly won't work at all. > > What you need to do is to rewrite things to work as standalone queries. > > For example, in your case I think you can do: > proc sql; > select mens into :menst > from mens_theo > where &totdu. between totdu_min and totdu_max > and prd_idt =3D &prd_idt.; > quit; > %put &menst; > > As long as that query returns a single value, it will work; if not it will > return the final value only. You can use > select mens into :menst separated by ' ' > from ... > if you want a space (or anything else inside the quotes) delimited list, if > it returns multiple rows. > > Then you have a macro variable &menst available to you at runtime and can d= > o > whatever you like with it. > -Joe > > > > On Mon, Mar 1, 2010 at 10:41 AM, J=E9r=F4me <jvanhe...@gmail.com> wrote: > > Hi, this my code > > > /*contracts data */ > > data tab1; > > input cnt_idt prd_idt cpt_totdu per_idt; > > datalines; > > 10 201 150 201001 > > ; > > run; > > /*parameters data*/ > > data mens_theo; > > input prd totdu_min totdu_max mens; > > datalines; > > 201 0 100 10 > > 201 101 1000 120 > > ; > > run; > > /*search parameter by product and totdu*/ > > %macro f_mens_theo(prd_idt,totdu); > > proc sql; > > select mens > > from mens_theo > > where &totdu. between totdu_min and totdu_max > > and prd_idt =3D &prd_idt.; > > quit; > > %put mens=3D ; > > %mend f_mens_theo; > > /*create output table */ > > data t; > > set tab1; > > J =3D cpt_totdu; > > do while(J gt 0); > > put J=3D; > > ; > > per_idt =3D %ajoute_mois(per_idt,1); > > %let _prd_idt =3D %eval(prd_idt); > > menst =3D %f_mens_theo(&_prd_idt.,cpt_totdu); > > j =3D j - menst; > > output; > > end; > > put "do J: " J=3D; > > run; > > > What I would like to have : > > how can I call %f_mens_theo with the good parameters (prd_idt and > > cpt_totdu from tab1 table) and return the value from the sql query. > > I tried some thing but no success. > > > Explanation : > > menst =3D %f_mens_theo(201,150); > > menst =3D 10; > > > Thanks in advance. > > > jerome- Hide quoted text - > > - Show quoted text -