topic Re: UPCASE ALL VARIABLES IN DATASET in SAS Procedures

UPCASE ALL VARIABLES IN DATASET

Doyleuk — Wed, 13 May 2009 09:35:37 GMT

Hi,

I was wondering if anyone can supply code that allows me to UPCASE all variables in a dataset.

Currently i have a macro which loops through each of the names and upcases them 1 at a time in a data step.

I was hoping there was an easier way to do this.

Like so

proc datasets library = cwork nodetails ;
contents noprint data = &final_name out = work.test3 ;
run;quit;

proc sql noprint;
select Count(*)
into :No_obs
from test3;
select NAME
into :KEEP_VAR1-:KEEP_VAR%left(&No_obs)
from test3;
quit;

%do k=1 %to &No_obs;

%let Keep_Var1 = &Keep_Var&k;

data cwork.&final_name;
set cwork.&final_name;
&Keep_Var1 = upcase(&Keep_Var1);
run;

Re: UPCASE ALL VARIABLES IN DATASET

LinusH — Wed, 13 May 2009 10:54:15 GMT

There are no syntax available to my knowledge that will do this.
But I think you are on the right track.
You could either in your SQL and perhaps by subsequent string manipulation prepare UPCASE assignments for all variables,
or do your macro loop inside your data step so you don't have to rewrite your SAS table for each variable to upcase.

/Linus

Re: UPCASE ALL VARIABLES IN DATASET

sbb — Wed, 13 May 2009 12:26:42 GMT

From what you have explained, it appears that you want to UPCASE the variable "values", not the variable "names" in your SAS datasets. You can use a DATA step with an ARRAY and reference _CHARACTER_ as the list of variables in the ARRAY. Then code a DO I=1 TO DIM(); END; and do your UPCASE re-assignment.

You will find useful topic-oriented information at the SAS support http://support.sas.com/ website, using the SEARCH facility.

Scott Barry
SBBWorks, Inc.

SAS 9.2 DOC: Array Processing
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002299816.htm

Variations on Basic Array Processing
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a000739610.htm

Re: UPCASE ALL VARIABLES IN DATASET

Florent — Thu, 14 May 2009 11:14:31 GMT

Hi,

Here is a sample code I created to do the job 😉
You just need to update the parameters which are passed into the macro call.

Enjoy !

Kind regards,
Florent

%MACRO UpcaseCharVar(lib, ds); /* lib: library's name
ds: dataset's name */
proc sql noprint;
select count(distinct name)
into :NbrCharVar
from dictionary.columns
where libname = upcase("&lib")
and memname = upcase("&ds")
and type = 'char';
quit;

%let NbrCharVar = &NbrCharVar; /* Removes Leading and Trailing Blanks */

proc sql noprint;
select name
into :CharVar1-:CharVar&NbrCharVar
from dictionary.columns
where libname = upcase("&lib")
and memname = upcase("&ds")
and type = 'char';
quit;

%IF &NbrCharVar > 0 %THEN
%DO i=1 %TO &NbrCharVar;
proc sql;
update &lib..&ds
set &&CharVar&i = upcase(&&CharVar&i);
quit;
%END;
%MEND;

%UpcaseCharVar(Work, Test_DS);

Re: UPCASE ALL VARIABLES IN DATASET

Doyleuk — Fri, 15 May 2009 10:59:19 GMT

Many Thanks.

Your code worked a treat

UPCASE ALL VARIABLES IN DATASET

Privet3711 — Fri, 13 Jan 2012 20:56:56 GMT

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);

UPCASE ALL VARIABLES IN DATASET

Howles — Fri, 13 Jan 2012 22:07:24 GMT

No need for a macro. Try this model.

data have ;

set sashelp.class ;

* keep _numeric_ ;

run ;

data want ;

set have ;

array charvar

_character_ ;

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.

Re: UPCASE ALL VARIABLES IN DATASET

Tom — Sat, 14 Jan 2012 03:29:41 GMT

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;

Re: UPCASE ALL VARIABLES IN DATASET

MikeZdeb — Sun, 15 Jan 2012 16:45:14 GMT

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

Re: UPCASE ALL VARIABLES IN DATASET

art297 — Sun, 15 Jan 2012 19:01:50 GMT

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

Re: UPCASE ALL VARIABLES IN DATASET

Ksharp — Mon, 16 Jan 2012 03:14:21 GMT

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

Re: UPCASE ALL VARIABLES IN DATASET

Tammboy — Tue, 13 Aug 2024 09:25:29 GMT

There is a SAS option for this:

option validvarname=upcase;

Re: UPCASE ALL VARIABLES IN DATASET

Sarath_A_SAS — Thu, 05 Sep 2024 23:46:55 GMT

You can also so this using PROC DATASETS and FORMAT

You can use PROC DATASETS to modify the dataset without having to explicitly loop through the variables.

Applying Uppercase Formatting to Character Variables

In this method, you use the $upcase. format to ensure that all character variables in your dataset are stored in uppercase format. The $upcase. format is predefined in SAS and will automatically convert all character data to uppercase.

proc datasets lib=work nolist;
    modify original_dataset;
    format _character_ $upcase.;
run;
quit;

Re: UPCASE ALL VARIABLES IN DATASET

Patrick — Mon, 09 Sep 2024 01:17:27 GMT

@Sarath_A_SAS

You're replying to a very old discussion here....

In this method, you use the $upcase. format to ensure that all character variables in your dataset are stored in uppercase format.

Actually: Using format $upcase. will DISPLAY/PRINT the characters uppercase but it will not change how they are stored. If you want to change how they are stored you need to rewrite the values using code like:

data all_upper;
  set sashelp.class;
  array cvars {*} _character_;
  do _i=1 to dim(cvars);
    cvars[_i]=upcase(cvars[_i]);
  end;
  drop _i;
run;