How to import all data in uppercase of excel file?

[varshabansal]

I want to import excel sheet which have multiple sheet and load the sheet's data in uppercase (i.e. everything in uppercase- variables and observations all ).

[PaigeMiller]

Do the import into a SAS data set, then you can convert everything to uppercase using the UPCASE function.

data want;
    set have;
    array text _character_;
    do i=1 to dim(text);
        text(i)=upcase(text(i));
    end;
run;

[andreas_lds · Accepted solution]

Can you post an example of how the resulting dataset should look like?

Using sashelp.cars a data step changing all alphanumeric vars to uppercase could look like this:

/* untested */
data want;
  set sashelp.cars;
  array cvars _character_;
  do i = 1 to dim(cvars);
    cvars[i] = upcase(cvars[i]);
  end;
  drop i;
run;

[Ksharp]

The following example is copying sheets of excel "c:\temp\date.xlsx" into WORK library.

Change the path of excel file and libname according to yours.

options validvarname=any validmemname=extend;

libname x xlsx 'c:\temp\date.xlsx';


filename x temp;
proc cport library=x file=x OUTTYPE=UPCASE;
run;
proc cimport library=work infile=x;
run;

libname x clear;