Macro perfoms slow on large datasets with large number of char variables
I modified it
%MACRO UPCASE_ALL(LIB, DS);
PROC SQL NOPRINT;
SELECT STRIP(NAME)||" = UPCASE( "||STRIP(NAME) || ");"
INTO :CODE_STR
SEPARATED BY ' '
FROM DICTIONARY.COLUMNS
WHERE LIBNAME = UPCASE("&LIB")
AND MEMNAME = UPCASE("&DS")
AND TYPE = 'char';
QUIT;
DATA &DS;
SET &DS;
&CODE_STR
RUN;
%MEND;
%UPCASE_ALL(WORK,INCD2);
No need for a macro. Try this model.
data have ;
set sashelp.class ;
* keep _numeric_ ;
run ;
data want ;
set have ;
array charvar
do i = 1 to dim(charvar) ; drop i ;
charvar(i) = upcase( charvar(i) ) ;
end ;
run ;
Note that if there happen to be no character variables, a WARNING appears. You may want to bulletproof for that situation.
Here are a couple of tricks to make the code shorter (and in my opinion clearer).
To eliminate the need to drop the index variable you can use the special variable _N_.
data want ;
set have ;
array _char _character_;
do _n_ =1 to dim(_char);
_char(_n_)=upcase(_char(_n_));
end;
run;
Or use the DO OVER syntax. This is clearer for this type of problem as the index has no meaning for this array.
data want ;
set have ;
array _char _character_;
do over _char ;
_char=upcase(_char);
end;
run;
You could add and drop a character variable to prevent the warning.
data want ;
length _dummych $1;
drop _dummych;
set have ;
array _char _character_;
do _n_ =2 to dim(_char);
_char(_n_)=upcase(_char(_n_));
end;
run;
hi ... another possibilty (if you don't mind changing the variable names to uppercase in the process) ...
* from ... http://support.sas.com/documentation/cdl/en/nlsref/61893/HTML/default/viewer.htm#a002473805.htm;
proc trantab table=ascii;
replace 'a' 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
save table=upper;
quit;
data test;
input name : $10. Gender : $1. CITY : $10. age;
datalines;
Mike m Albany 25
Art m Toronto 30
;
proc cport data=test file='z:\test.cpt';
trantab name=upper;
run;
proc cimport data=test_new file='z:\test.cpt';
run;
proc print data=test_new;
run;
Obs NAME GENDER CITY AGE
1 MIKE M ALBANY 25
2 ART M TORONTO 30
Mike, Nice approach and this time I definitely can follow the logic. I thought you might have been referring to you and me in the example data .. until I saw the ages listed
Mike . You do not need to make translate table.
In proc cport ,there is already an option to do this.
filename tran 'c:\x.dat'; proc cport library=sashelp outtype=upcase file=tran memtype=data; select class; run; proc cimport infile=tran library=work;run;
Ksharp
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.