topic Convert observation to ASCII in SAS Programming

Convert observation to ASCII

Jeff_TH — Fri, 10 Jun 2016 08:36:52 GMT

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;

Re: Convert observation to ASCII

Kurt_Bremser — Fri, 10 Jun 2016 08:55:49 GMT

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)

Re: Convert observation to ASCII

RW9 — Fri, 10 Jun 2016 09:02:43 GMT

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.

Re: Convert observation to ASCII

Jeff_TH — Fri, 10 Jun 2016 13:23:06 GMT

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!

Re: Convert observation to ASCII

RW9 — Fri, 10 Jun 2016 13:35:54 GMT

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;

Re: Convert observation to ASCII

Jeff_TH — Fri, 10 Jun 2016 13:45:06 GMT

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

Re: Convert observation to ASCII

RW9 — Fri, 10 Jun 2016 13:59:44 GMT

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;

Re: Convert observation to ASCII

Jeff_TH — Fri, 10 Jun 2016 14:04:06 GMT

Dear RW9,

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

Have a good day!

Re: Convert observation to ASCII

Jeff_TH — Tue, 14 Jun 2016 08:09:06 GMT

Dear RW9,

Many thanks! You are wonderful!

topic Convert obser​vation to ASCII in SAS Programming

Convert obser​vation to ASCII

Re: Convert obser​vation to ASCII

Re: Convert obser​vation to ASCII

Re: Convert observation to ASCII

Re: Convert observation to ASCII

Re: Convert obser​vation to ASCII

Re: Convert obser​vation to ASCII

Re: Convert observation to ASCII

Re: Convert observation to ASCII

topic Convert observation to ASCII in SAS Programming

Convert observation to ASCII

Re: Convert observation to ASCII

Re: Convert observation to ASCII

Re: Convert observation to ASCII

Re: Convert observation to ASCII