BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jeff_TH
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
Kurt_Bremser
Super User

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
Calcite | Level 5
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5
Dear RW9,

Many thanks! You are wonderful!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5
Dear RW9,

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

Have a good day!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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