Hi Ksharp and Haikuo, I am learning hash. Is "link=id" in "link=id;period=period-1;var2=var1;" necessary? Thanks - Linlin ... View more

Hi Ksharp and Haikuo, Your hash solutions are really nice. I used datastep and merge to help me to understand how hash works. Thank you!  - Linlin data have; input ID$    Period    Var1   Var2    Start_Lag    End_Lag   ; k=_n_; group=first(id); cards; a1    1   500    300    .   . a2    1   450    350    .   . a3    2   350    300    1   2 a4    2   300    200    2   3 a5    3   300    50    1   2 a6    3   200    100    2   3 b1    1   525    175    .   . b2    1   451    321    .   . b3    1   226    216    .   . b4    2   321    101    1   3 b5    2   216    106    2   4 b6    2   175    105    3   5 ; run; data temp1;   set have(rename=(id=link k=k1));      drop Start_Lag End_Lag var1;   proc sort;   by group period var2; run; data temp2;   set have;     period=period-1;var2=var1; proc sort;   by group period var2; run; data want(keep=id link);   merge  temp2(in=a)temp1(in=b);   by group period var2;   if a;   if not b then link='';   if not (start_lag <= k-k1 <= end_lag) then call missing(link); proc sort;    by id; run; proc print;run;                                   obs    ID   link                                    1    a1                                    2    a2                                    3    a3    a2                                    4    a4    a1                                    5    a5    a3                                    6    a6    a4                                    7    b1                                    8    b2                                    9    b3                                   10    b4    b2                                   11    b5    b3                                   12    b6    b1 ... View more

Thank you Tom!  I will try to remember that.   - Linlin ... View more

Hi Tom, why " if Fact_name=%scan(&nm,&i,*) then output new&i;" not "if Fact_name=%scan(&nm,&i,"*") then output new&i;" (it doesn't work) ? Thank you!  - Linlin ... View more

Thank you PG! I have updated the code.  - Linlin ... View more

Hi Hima, Thank you for your encouragement! We learn from each other. Have a nice weekend! - Linlin ... View more

or data have; input date num 3.; informat date mmddyy8.; cards; 01/8/2012 9 02/2/2012 5 01/7/2012 9 03/2/2012 8 01/31/2012 10 01/30/2012 6 ; run; proc sql; create table want as       select  intnx("month",date,0,"beginning") as month format=mmyyd7.           ,sum(num) as total                          from have                             group by month; quit; Updated after PG's comments ... View more

borrowed data from Hima and code from PGstate: data have; input date num 3.; informat date mmddyy8.; cards; 01/8/2012 9 02/2/2012 5 01/7/2012 9 03/2/2012 8 01/31/2012 10 01/30/2012 6 ; run; proc sql; create table periods as select distinct intnx("month",date,0,"beginning") as period format=mmyyd7., intnx("day",date,0,"beginning") as periodBeg format=mmddyy10., intnx("day",date,0,"end") as periodEnd format=mmddyy10. from have; create table want as select period, sum(num) as total from periods inner join have on date between periodBeg and periodEnd group by period; drop table periods; quit; proc print data=want;run;                                    Obs     period    total                                       1     01-2012      34                                       2     02-2012       5                                       3     03-2012       8 Linlin ... View more

How about: (note: libname and memname must be capital letters) proc sql noprint;    select name into : names separated by ' '    from dictionary.columns    where libname='WORK' and memname='HAVE' and upcase(name) like '%_123' ; quit; data want;   set have(keep=&names); run; Linlin ... View more

you may find something information from the bottom part of the link https://communities.sas.com/message/119093#119093.  there are options (such as VALIDVARNAME) to control cases. ... View more