Though the solution is already provided, here is an alternative approach.    Hope you like it.   data want; set have(rename=v1=_v1); retain v1; by id; if first.id then v1=_v1; else v1=sum(v1,v2); run;   ... View more

Try this using ranges excluding one of the values of the range: proc format;   value V1format   low-<-0.875 = 'A'   -0.875-<-0.625 = 'B'   -0.625-<-0.375 = 'C'   -0.375-<-0.125 = 'D'   -0.125-0 = 'E'   0<-0.125 = 'F'   0.125<-0.375 = 'G'   0.375<-0.625 = 'H'   0.625<-0.875 = 'I'   0.875<-high = 'J'; run; You can test your format with this small program: data test;   do number=-880 to 880 by 2;     x=number/1000;     y=put(x,V1format.);     output;   end; run; Regards ... View more

Dear , I'm not pissing at @RW9. I immediately thanked him for his help and selected his answer as the correct one. I just posted a different version of the code for future users. :smileygrin: ... View more

Thank you guys for all your help. ... View more

Thank you so much ! ... View more

If I read your description correctly you want a join not a Cartesian product proc sql; create table want as select &big.*, small.id from &big. left join small on &big..dt = small.dt; quit; ... View more

data want;    set db1;    if v1=0 then do;        output;      if lag(v1) <> 0 then output; end; run; (caution:  lag can give false results if called from a conditional dataset) ... View more

Hi Mark, Thanks for your response i just checked the code i suggested to you and i see it is producing your expected output. like it is generating the same number of observations similar to db1 by matching on id and date variables. could you please again check and correct me. data db1;      input id date V1 V2; datalines; 1     20110902     0.5     2.1 2     20110903     0.7     2.8 ; data db2;      input id date V3; datalines; 1     20110901     10 1     20110902     20 1     20110903     30 2     20110901     60 2     20110902     50 2     20110903     40 ; proc sort data=db1; by id date; run; proc sort data=db2; by id date; run; data db3;   merge db1(in=a) db2(in=b);   by id date; if a; run; Thanks, Jag ... View more

Howard, To confess ... I did read the question and did run to fast into those words of "changing format"  oops...( should say sorry) Seeing afterwards the question was also a variable type conversion. I left my answer as a fit for the original question. The best thing to do (good practice) could be solving the type when data is coming in.  Infile/input options access/dbtype. The combining to an analytics dataset (by where processing) is an advice I agree. I looked at your nice paper http://support.sas.com/resources/papers/proceedings14/1277-2014.pdf The sum statement is not well known  SAS(R) 9.4 Statements: Reference, Third Edition. I often fail to remember that one. The scope to just the SAS environment is an important one as you indicated, external dbms may support something. Advice of not using the monotonic function as not documented not reliable agree on that as good practice. Working out 5 approaches with a performance comparison, marvelous. Just a pitty a of the performance of the last two. But it is an indication what will happen if the dataset becomes larger.   A long introduction... There is something missing, that is: "what the effects are of the multi-threading processing in proc sql  (Tk-kernel)?". This is new since 9. The monotonic function can not be reliable anymore when there are several threads parallel processed. Using the macro-environment for intermediate storage and counting is thinking on a possible parallel interruption break on the wrong moment. It will hardly be possible to test or validate something like that. I think of an interrupt between symget/symput (FCMP usage).  The probability that it will happen is almost zero, but is it really zero?    ... View more

Thank you guys!! Both solutions work. ... View more