Convert obser​vation to ASCII

[Jeff_TH]

Hello,

I want to write a script to sum all ascii values by the column on SAS dataset and a script sample like follows.

Actually, the script can be run normally with generate result successfully.

But I face a problem that the performance is very slow if the number of selections are over milliion.

Could some one help me to settle the performance issue? Many thanks!

%macro sqlloop;
%LET numrecords  = 0;
 PROC SQL  noprint;
select count(*) into :numrecords
from test.convertsac where  flag='Y' and tab_name like 'TB_%%';
quit;

%if &numrecords>0 %then %do;

 PROC SQL  noprint outobs=&numrecords;
select tab_name, col_name
into :tab1 - :tab%left(&numrecords), :col1 - :col%left(&numrecords)
from  test.convertsac where flag='Y' and tab_name like 'TB_%';

%DO x=1 %TO &numrecords;
%let tbname  = &&tab&x ;
%let colname  = &&col&x ;
select count(*) into :numobs
from test.&tbname where &colname is not null;
quit;
 %PUT &tbname &colname   =======START========;
  %LET totalasc  = 0;
  %if &numobs>0 %then %do;
    PROC SQL  noprint outobs=&numobs;
    select &colname
    into :tmp1 - :tmp%left(&numobs)
     from test.&tbname  where &colname is not null;
   
    %DO k=1 %TO &numobs;
      %LET STRING= %sysfunc(TRIM(&&tmp&k));
      %LET ascsum = 0;
      %do i = 1 %to %length(&STRING); 
           %let ischara=%substr(&STRING,&i,1);
         %let ischara=%str(&ischara);  
                    %let asc =%sysfunc(rank(%bquote(&ischara))); 
         %if &asc=0 %then %do;
           %let asc =32;
           %end;
           %let ascsum=%eval(&ascsum + &asc) ;
        %end;
      %LET totalasc=%eval(&totalasc + &ascsum) ;
     %END;
     %PUT &tbname &colname &totalasc  =======END======== PROGRESS= &x  / &numrecords;
   %end;
     %else  %do;   
     %PUT &tbname &colname &totalasc   =======END======== PROGRESS= &x  / &numrecords;
   %end;
PROC SQL noprint;
update test.convertsac
set ascii=&totalasc, flag='N'
where tab_name ="&&tbname" and col_name="&&colname";
%END;
%END;
 QUIT;
 %mend;
 %sqlloop;

[Kurt_Bremser]

At first glance, this looks like another monstrous abuse of the macro language.

The SAS macro language is for creating dynamic program code, not for solving data issues. That is best done in SAS data step language.

Aside from the piss-poor performance, you get an honorable mention for the Obfuscated SAS Code Contest.

Since it is very hard to extract your intentions from the code, could you rephrase your original requirement?

Do you want to add up all the ASCII character values in the strings of a given dataset (or a number of datasets)?

You can get all dataset metadata from SASHELP.VTABLE and SASHELP.VCOLUMN (or DICTIONARY.TABLES and DICTIONARY.COLUMNS in SQL)

[Jeff_TH]

Hi KurtBremser,

Thanks for the reply


Do you want to add up all the ASCII character values in the strings of a given dataset

Yes, I just want to add up all the ASCII character values in the strings of a given dataset.

Do you have any idea on it?

Thanks!

[RW9]

So you have to convert, in which case:

data have;
  abc="a"; def="h"; output;
  abc="d"; def="z"; output;
run;
data _null_;
  set have (obs=1);
  array vals _character_;
  call execute('data converted; set have;');
  do over vals;
    call execute(cats(vname(vals),"_n=rank(",vname(vals),");"));
  end;
  call execute(';run;');
run;
proc summary data=converted;
 var _numeric_;
 output out=results sum=;
run;

[Jeff_TH]

Dear RW9,

Many thanks! You are wonderful!

[RW9]

Could you not use one of the procedures designed for the purpose - proc means, summary, freq?  Not entirely sure I know what you mean by "sum all ascii values", is this character fields with numbers in - if so then use input() to read in the character and convert to numeric, if they are numerics then you wouldn't even need to convert.  Post example test data (in the form of a datastep) and what the output should look like.

[Jeff_TH]

Dear RW9,


Many thanks for your reply!


Basically, I just want to convert all string data values to ASCII values and add up all of them.


For example I have a dataset as below:


ABC

AD

ZY

X$M


I expect the SAS program can output value is: 711

[RW9 · Accepted solution]

Thats even easier then - my program posted before assumed multiple values, this one assumes just the on per your example:

data have;
  val="ABC"; output;
  val="AD"; output;
  val="ZY"; output;
  val="X$M"; output;
run;

data want;
  set have end=last;
  if _n_=1 then result=0;
  retain result;
  do i=1 to lengthn(val);
    result=sum(result,rank(char(val,i)));
  end;
  if last;
run;

[Jeff_TH]

Dear RW9,

You are wonderful, many thanks for your prompt action!!

Have a good day!