data have; input acct    flag; cards; 14710    1 14710    0 14710    1 49730    1 49730    0 49730    1 15293    1 15293    1 1147    1 1147    0 1147    0 13664    1 13664    1 13664    1 ; run; proc sort data=have; by acct descending flag; run; data want;   do i=1 by 1 until (last.acct);   set have;   by acct;   array vars[100] _temporary_;   vars(i)=flag;   count+1;   if last.acct then do;      if count(cats(of vars(*)),'1')=count then output;      else do;          do j=1 to count(cats(of vars(*)),'0');                output;          end;       end;      end;    end; call missing(of vars(*),count); drop i j count; run; ... View more

Also try this: %let path=c:\temp\; data _null_; infile "dir/b/a:-d  ""&path"" " pipe truncover; length ren $500.; input name $20.; newname=prxchange('s/test(14|0)/test14/',-1,name); ren=catx(' ','rename',quote(cats("&path",name)),quote(newname)); infile ren pipe filevar=ren; run; ... View more

data want;   set have;   by acct_nbr;   retain temp _trig_cd flag;   if first.acct_nbr then do;    temp=id;    _trig_cd=trig_cd;    end;    if temp^=id then flag=1;   if last.acct_nbr then do;      if id=temp and _trig_cd='NAC' and flag=1 then switch=1;   else switch=0;   output;    end;   drop temp _trig_cd flag; run; Message was edited by: Shenglin Chen ... View more

data Group_A; input set units; cards; 1  5                          2  6 3  258 ; data Group_B; input set units; cards; 1  4 2  8 3  12 5  5    ; data Group_C; input set units; cards; 1  3 2  4 3  16 4  8 ; run; proc sql;   create table sets as   select coalesce(d.set,c.set) as set from (select coalesce(a.set,b.set) as set from group_A a inner join group_B b on a.set=b.set) d  inner join group_C c   on d.set=c.set;   quit;   data want;   if 0 then set group_A group_B group_C;   if _n_=1 then do;     declare hash h_a(dataset:'group_A');     h_a.definekey('set');     h_a.definedata('set','units');     h_a.definedone();     declare hash h_b(dataset:'group_B');     h_b.definekey('set');     h_b.definedata('set','units');     h_b.definedone();     declare hash h_c(dataset:'group_C');     h_c.definekey('set');     h_c.definedata('set','units');     h_c.definedone();    end;    count=0;     do until (last);      set sets end=last;      retain count;      h_a.find();;         count+units;         if count>300 then leave;        else output;       h_b.find();      count+units;      if count>300 then leave;       else output;      h_c.find();      count+units;      if count>300 then leave;      else output;     end;      run; ... View more

data have; input CODE $ cluster_id a1 a2 a3 a4 a5; datalines; a1 1 0 0.19516 0.19242 0.24021 0.32064 a2 2 0.19516 0 0.22981 0.30144 0.31116 a3 2 0.19242 0.22981 0 0.10592 0.11456 a4 3 0.24021 0.30144 0.10592 0 0.16124 a5 3 0.32064 0.31116 0.11456 0.16124 0 ; run; data want;   do i=1 by 1 until (last);   set have end=last;   array temp  x1-x5 ;    temp(i)=cluster_id;    end;   do point=1 to nobs;   set have point=point nobs=nobs;   array vars a1-a5;    do i=1 to dim(vars);       retain vars;      retain cluster_id;      code_n=vname(vars(i));      cluster_idn=temp(i);      dist=vars(i);      output;   end; end; drop x: i a:; run; ... View more

it could solved the problem of path with space. filename files pipe "dir/b %bquote("&filepath.\*.xlsx")"; ... View more

The code work for me. 2    %let filepath=c:\test; 3 4    filename files pipe "dir/b %bquote(&filepath.)\*.xlsx"; 5 6    data _null_; 7 8    infile files truncover; 9 10   length filename $20.; 11 12   input; 13 14   filename=scan(_infile_,1); 15 16   call execute( 17 18   'proc import out='||filename|| 18 ! 'datafile="%bquote(&filepath.)\'||strip(filename)||'.xlsx" dbms=xlsx 18 ! replace; 19 20   run;' 21 22   ); 23 24   run; NOTE: The infile FILES is:       Unnamed Pipe Access Device,       PROCESS=dir/b c:\test\*.xlsx,RECFM=V,       LRECL=32767 NOTE: 2 records were read from the infile FILES.       The minimum record length was 9.       The maximum record length was 10. NOTE: DATA statement used (Total process time):       real time           0.10 seconds       cpu time            0.04 seconds NOTE: CALL EXECUTE generated line. 1   + proc import out=cars                datafile="c:\test\cars.xlsx" dbms=xlsx replace; 1   + run; NOTE: The import data set has 428 observations and 15 variables. NOTE: WORK.CARS data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time):       real time           0.08 seconds       cpu time            0.09 seconds 2   + proc import out=class               datafile="c:\test\class.xlsx" dbms=xlsx replace; 2   + run; NOTE: The import data set has 19 observations and 5 variables. NOTE: WORK.CLASS data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time):       real time           0.01 seconds       cpu time            0.01 seconds ... View more

Change to: %let year1 =%sysfunc(intnx(month,%sysfunc(today()),-12),date9.); ... View more