Help using Base SAS procedures

Upcase all data in the Excel file

Reply
N/A
Posts: 0

Upcase all data in the Excel file

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Upcase all data in the Excel file

Posted in reply to deleted_user
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
Contributor DCL
Contributor
Posts: 40

Re: Upcase all data in the Excel file

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 2 replies
  • 137 views
  • 0 likes
  • 3 in conversation