DATA Step, Macro, Functions and more

Convert obser​vation to ASCII

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Convert obser​vation to ASCII

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;


Accepted Solutions
Solution
‎06-15-2016 10:38 AM
Super User
Super User
Posts: 7,407

Re: Convert obser​vation to ASCII

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;

View solution in original post


All Replies
Super User
Posts: 6,946

Re: Convert obser​vation to ASCII

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)

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Convert observation to ASCII

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!


Super User
Super User
Posts: 7,407

Re: Convert observation to ASCII

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;
Occasional Contributor
Posts: 5

Re: Convert observation to ASCII

Dear RW9,

Many thanks! You are wonderful!

Super User
Super User
Posts: 7,407

Re: Convert obser​vation to ASCII

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.

 

Occasional Contributor
Posts: 5

Re: Convert obser​vation to ASCII

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

Solution
‎06-15-2016 10:38 AM
Super User
Super User
Posts: 7,407

Re: Convert obser​vation to ASCII

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;
Occasional Contributor
Posts: 5

Re: Convert observation to ASCII

Dear RW9,

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

Have a good day!
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 419 views
  • 3 likes
  • 3 in conversation