This is perfect! Many of the other solutions I have tried were removing the leading zeros, the trailing zeros, all zeros, or a combination. I need something that removes only the leading zeros in a padded account number text field and then I was able to make that a numeric varialbe. The data was migrated poorly from a mainframe system to a SQL DB, so this helps a ton! Thanks so much! ... View more

data sda; input ptno visit weight; format ptno z3. ; cards; 1 1 122 1 2 . 1 3 . 1 4 123 2 1 156 2 3 . 3 1 112 3 2 . 4 1 . 4 2 123 4 3 . ; run; data all; format ptno z3.; do i=1 to 4; do j=1 to 4; ptno=i; visit=j; output; end; end; drop i j; run; proc sort data=sda; by ptno visit; run; proc sort data=all; by ptno visit; run; data final (drop=tempval); retain tempval 0; merge sda(in=b) all (in=val); by ptno visit ; if val; if weight eq . then weight=tempval; else tempval=weight; run;   Output is as below   001 1 122 001 2 122 001 3 122 001 4 123 002 1 156 002 2 156 002 3 156 002 4 156 003 1 112 003 2 112 003 3 112 003 4 112 004 1 112 004 2 123 004 3 123 004 4 123 ... View more

In the SAS Macro doc and I believe also in the SAS Concepts doc is a detailed description of how the SAS Macro and SAS Language processors interact. It's very important to understand this. As for your question: Lets assume "&n" has a value of "2": a) resolves on macro level "&n". The SAS language sees then: if 2=1 then call symput("record1",records); b) tests the macro %if condition on macro level. As the condition is not true the SAS language "sees" nothing. If the condition would be true the SAS language would see: call symput("record1",records); An alternative way of coding only creating the macro variable for the current value of "&n": %macro test(n=);   %global record&n;   data  want&n.;      set have (obs=&n.);      call symputx("record&n",records);   run; %mend test; ... View more

ups, the first run need to be removed , and the code will be OK. ... View more

Thanks ... View more

Thank you very much  Tom, it works. ... View more

You need to keep another variable to break the ties, such as the original text variables, along with the frequency variables so that you can include them in the sort order.  So if you have FOOD=5 and DRINK=5 then FOOD will come after DRINK because F comes after D.  Similarly for the ties between MEAT and VEGETABLES. order by freqvalue descending        , value        , freqitem descending        , item        , flag descending ... View more

Whatg do you think about this one? I am wondering if I can simplify step (merge proc sql) and (proc sort) in only only an  proc sql...Thanks in advance. ***sum of severities by value and intensity; proc sql noprint; create table want2 as select upcase(value) as value, intensity,nintensity,color, ncolor, sum (nintensity) as n1 from have group by value,intensity order by value, color, intensity descending; quit; **flaging priority (Severe>Moderate>Mild); data havex;   set have;   if upcase(intensity)="SEVERE" then flag=3;     else if upcase(intensity)="MODERATE" then flag=2;    else if upcase(intensity)="MILD" then flag=1; run; ***first proc sql to create the order of value in the variable label; proc sql noprint; create table want2x as select value, intensity,nintensity,color, ncolor, sum(flag) as col1 from havex group by  value, color order by value, color, intensity descending; quit; ***second proc sql to create the order of color in the variable label proc sql noprint; create table want3x as select value, intensity,nintensity,color, ncolor, sum(flag) as col0 from havex group by value order by value, color, intensity descending; quit; data want3; merge want2x want3x; by value color; run; proc sort data=want3 out=want3s;by descending col0 descending col1 color descending intensity;run; **create the variable label; data want4 (keep=label value color count count2); set want3s; by descending col0 descending col1; if first.col0 then do; label=upcase(value); count+1; count2=0; output; end; if first.col1 then do; label=color; count+1; count2=1; output; end; run; **merge proc sql; proc sql noprint; create table want5 as select  a.label,a.count,a.count2,b.color,b.ncolor,b.intensity,n1 from want4 as a left join want2 as b on a.label=b.value or a.color=b.color order by count,color, intensity descending; quit; ***proc sort; (I am wondering if I can include this step in the above step);  Need help for this one proc sort data=want5 out=want5s nodupkey; by count descending intensity; run; Final dataset wantx; data wantx (keep=label intensity ncolor); set want5s; by count; if count2=0 then ncolor=n1; run; ... View more

sorry Tom...it was an easy twist...Thank you for all your help. proc sql noprint ;   create table report1 as     select       max(nsoc) as nsoc      ,max(nsoc2) as nsoc2      ,npterm   ,npterm2      ,soc      ,pterm     from temp     group by soc,pterm     order by 1 desc, 2 desc   ; quit; data want (keep=label count1 count2);   set report1 ;   by descending nsoc ;   if first.nsoc then do;     label=soc; count1=nsoc;count2=nsoc2; output;   end;   label=pterm; count1=npterm;count2=npterm2; output; run; proc print data=want;     var label count1 count2; run; ... View more

SAS will fill in the values.  Sometimes it fills them in somewhat differently than you might want Steve Denham ... View more

Brilliant Hai.Kuo Many thanks. Cheers, V. ... View more