You probably can remerge your proc freq output to get what your want. Here is to show you 2xDWL will do the trick as well: data have; infile cards; input ID $ Date :$8. Price Quantity; cards; 1 20001001 1.2 5.6 1 20001002 1.3 8.9 1 20001002 1.3 9.9 2 20101110 2.9 3.3 2 20101111 1.5 2.1 3 19990314 1.6 0.9 3 19990314 1.6 0.8 3 19990315 1.7 0.7 ; data want;   do _n_=1 by 1 until(last.date);      set have; by id date;   end;     do until(last.date);      set have; by id date; frequency=_n_; output;   end; run; proc print;run; Regards, Haikuo ... View more

Try this: instead of : char_code = put(ds_code, 7.); use this:   char_code = put(ds_code, $7.); Haikuo ... View more

Here just to start conversations: data have; infile cards; input ID     TranDate :yymmdd10.     Product $     Region $     SubRegion $; format trandate yymmdd10.; cards; 100     2009-09-22     FOT     AT     SAT_1 100     2010-05-02     FOT     AT     SAT_2 100     2011-07-14     FOT     AT     SAT_3 100     2007-07-06     BIK     OM     SOM 100     2009-03-09     BIK     OM     SOM 200     2009-09-22     FOT     KN     SKN 200     2010-05-02     FOT     LZ     SLZ_1 200     2011-07-14     FOT     LZ     SLZ_2 300     2007-07-06     BIK     OM     SOM 300     2009-03-09     BIK     AT     SAT 400     2010-05-07     FOT     AT     SAT 400     2011-02-11     FOT     AT     SAT 500     2008-11-17     BIK     LZ     SLZ 500     2009-12-24     BIK     LZ     SLZ ; proc sql; create table want as select a.id,         count(distinct a.id) as ID_CT,         (select count(distinct id)          from (select id from have          group by id having count(distinct region)>1))as id_ct_r,         (select count(distinct id)          from (select id from have          group by id having count(distinct subregion)>1))as id_ct_sr, calculated id_ct_r / calculated id_ct as percent_R format percent7.2, calculated id_ct_sr / calculated id_ct as percent_SR format percent7.2, trandate_m, subregion_m        from have a left join (select id,  subregion as subregion_m, max(trandate) as trandate_M format yymmdd10.             from have  group by id having trandate=calculated trandate_m) b on a.id=b.id ; quit; proc print;run; I am also confused on your condition 2 ( - the total number of distinct ID that have multiple Regions) and 3 (- the total number of distinct ID that have multiple Regions and multiple subregions), aren't they logically  the same? Under the same ID, multiple regions will evitablely lead to multiple subregions, right? or you want to mean that each region has to have multiple subregion to be qualified? Haikuo ... View more

Like Tom said, choose the right Dxden can be tricky. Here is another version based on LinLin's code, it managed to meet op's need. One thing I don't like is that it is still involving some hard coding. data have; informat fsdt mmddyy10.; format fsdt mmddyy10.; input contract  :FSDT : DxDEN : HCC $8.; cards; 1001        01/01/2010            43002  HCC-108 1001        01/01/2010            43004  HCC-109 1001        03/01/2010            43004  HCC-109 1002        03/01/2010            43002  HCC-108 1003        04/01/2010            43004  HCC-109 1003        04/01/2010            43006  HCC-90 1003        04/01/2010            43002  HCC-108 1003        05/01/2010            43002  HCC-108 ; data have; set have; order=input(substr(hcc,anyalpha(hcc,-10)+2),3.); run; proc sort data=have; by contract fsdt order; run; data want (drop=hcc dxden order rename=dx=DxDen);   do _n_=1 by 1 until (last.fsdt);       set have;       by contract fsdt order;       array h $ hcc1-hcc3;       if first.fsdt then dx=dxden;       h(_n_)=hcc;   end;   output; run; proc print;run; ... View more

Here is another more general approaches in an attempt to  eliminate the hard coding: data have; infile cards; input ME $  ZP   YR    PL; cards; m1 7501 2001 85 m1 7502 2001 22 m1 7503 2001 45 m1 7504 2001 85 m1 7505 2001 11 m1 7505 2001 65 m2 7501 2001 70 m2 7502 2001 55 m2 7503 2001 09 m2 7504 2001 19 m2 7505 2001 33 m2 7505 2001 72 m3 7501 2001 70 m3 7502 2001 55 m3 7503 2001 09 m3 7504 2001 19 m3 7505 2001 33 m3 7505 2001 72 ; ; /*This to hash out multiple files base on ME*/ data _null_; length n_me $100; retain n_me;    declare hash _out(ordered: 'a');    _out.definekey ('_n_');    _out.definedata ('me','zp', 'yr', 'pl');    _out.definedone();    do _n_=1 by 1 until (last.me);       set have; by me; _out.add(); end; n+1; n_me=catx(' ',n_me,me); _out.output(dataset : 'out'||me); call symput('n',n); call symput('n_me',n_me); run; /*This to stich them together*/ %macro merg; %do i=1 %to &n; %if &i=1 %then %do; data want; set out%scan(&n_me,&i) (drop=me rename=pl=%scan(&n_me,&i)); run; %end; %else %do; data want; merge want out%scan(&n_me,&i) (drop=me rename=pl=%scan(&n_me,&i)); by yr zp; run; %end; %end; %mend; %merg proc print;run; Regards, Haikuo ... View more

Bob, By following Art's lead, I hope the following code will meet your need: data table_1; input name $ low $ high $; datalines ; A Y010 Y013 B B2R300 B2R305 C NN10 NN13 D 6503 6505 ; run; data want (keep=name from_to); set table_1; _l=put(strip(input(substr(low,ANYALPHA(low,-10)+1),4.)),4.); l=length(_l); put l; _h=substr(high,ANYALPHA(high,-10)+1); do _n_=_l to _h;    if ANYALPHA(low)=0 then from_to=strip(put(_n_,10.));    else  from_to=substr(low,1,length(low)-length(_l))||strip(_n_);    output; end; run; Regards, Haikuo ... View more

Well, I could not figure out how to do it in one data step. And I am sure someone will come up with more elegant approaches. data have; infile cards; input Havet     y; cards; 1     54 2     66 3     . 4     87 5     92 6     . 7     . 8     8 9     21 10     53 11     89 12     . 13     . 14     . 15     75 16     80 17     79 18     45 19     94 20     76 ; data want1 (drop=_:);   do _n_=1 by 1 until (eof);      set have end=eof; _lag_y=ifn(y ne ., y,_lag_y); lag_y=ifn(y ne ., .,_lag_y); output;    end; run; proc sort data=want1; by descending havet; run; data want2 (drop=_:);   do _n_=1 by 1 until (eof);      set want1 end=eof; _lead_y=ifn(y ne ., y,_lead_y); lead_y=ifn(y ne ., .,_lead_y);      new_y=ifn(y ne ., y,mean(lag_y,lead_y)); output;    end; run; proc sort data=want2; by havet; run; Regards, Haikou ... View more

Wow, it is a GREAT paper! I probably need more time to dwell on it, but Thanks, Art! ... View more

I know you were asking SQL solution and Art just offered one. In case data step is not completely out of picture, here is one example: data want; set have; points=ifn(points>1.05,1.05,points); run; Regards, Haikuo ... View more

Since they were extracted from older codes, so for both of the following, I have option '/b', meaning only the name will be shown. Just remove that option, you will have everything within that folder. Options noxwait; x 'dir *.*  x:\ /b /d >x:\test.txt'; filename test "c:\test.txt"; or use PIPE method to avoid permanent text file: %let path=x:; filename test pipe "dir /b &path\*.* " ; Remove the BOLD part, and try again. FWIW, I suggest you follow Art's link and learn it thoroughly. Regards, Haikuo ... View more