topic Re: Upcase all data in the Excel file in SAS Procedures

Upcase all data in the Excel file

deleted_user — Tue, 02 Nov 2010 12:59:04 GMT

Hello,

I need a piece of code which is capable to upcase all the datas in the Excel file.
But my aim is this: i want to automize for all the datasets. I mean it should work for all distinct dataset column names.

For example:
i got a excel file which has two columns: ID NAME
i got another excel file which has three columns: SURNAME SEX AGE

So my function should work for all files by defining the column names at the start of the code.
So i should say:
my columns which should be upcased is (ID,NAME) or (SURNAME,SEX,AGE)

And then this following code should upcase the column's data:

newID=upcase(ID);
newNAME=upcase(NAME);

Can someone help me to this job?

Thanx in advance,
Cakcan

Re: Upcase all data in the Excel file

sbb — Tue, 02 Nov 2010 14:15:20 GMT

One approach is to write a SAS macro that uses PROC SQL and DICTIONARY.COLUMNS to generate a list of SAS variable names to be used in a SAS DATA step for the UPCASE operation on each variable/observation value.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

generate code proc sql dictionary columns site:sas.com

Re: Upcase all data in the Excel file

DCL — Wed, 03 Nov 2010 04:13:28 GMT

I guess the key over here is to get the list of variable names from the dataset and then perform the upcase function.

Following macro could serve the purpose

%macro to_upcase(dataset);

data Upcase;
set &dataset;

%let num_vars=%sysfunc(attrn(&dsid.,nvars)); %let dsid=%sysfunc(open(&dataset,i));
%do i=1 %to &num_vars.;
%let vname=%sysfunc(varname(&dsid.,&i));

&vname=upcase(&vname);
%end;
%let rc= %sysfunc(close(&dsid.));
run;

%mend;

%to_upcase(<<dataset name>>);

Refer following documentation http://support.sas.com/documentation/cdl/en/lrdict/63026/HTML/default/viewer.htm#a000212040.htm

-D