Help using Base SAS procedures

Upcase all data in the Excel file

Posts: 0

Upcase all data in the Excel file


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:


Can someone help me to this job?

Thanx in advance,
Super Contributor
Super Contributor
Posts: 3,174

Re: Upcase all data in the Excel file

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
Contributor DCL
Posts: 35

Re: Upcase all data in the Excel file

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

%let rc= %sysfunc(close(&dsid.));


%to_upcase(<<dataset name>>);

Refer following documentation

Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation