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;
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;
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)
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;
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.