Automation of
Changing Item ID

Chong-ho (Alex) Yu, Ph.D.


The write-up entitled Writing efficient SAS codes recommends that one should use consecutive numbers in variable assignment, such as Q1-Q100, instead of typing all one hundred variable names. But the original data sets contain meaningful IDs. It is not difficult to rename item IDs in a data set, but it is tedious to repeat the same procedure in a few hundred data sets.


The following codes work though it may not be the best:

/* assume that you have assign a path into a macro variable called "path," and you have a dataset called "finaloutput," which uses consecutive numbers as temporary ID, such as temp1-tempx, and a dataset called listitem, which contains the real itemids.*/

/* obtain the item list and put the last item on top of the list */

data listitem; set listitem;

position = _N_;

proc sort; by descending position; run;

/* assign the position of the last item as a global macro variable */

data listitem; set listitem;

/* use the following syntax if SAS version 9 is used. Symputx can trim the extra space in the macro variable*/

if _N_ = 1 call symputx("lastitem", position); run;

/* use the following syntax if SAS version 8 or below is used. Extra space will remain in the macro*/

if _N_ = 1 call symput("last", position); run;

/* If symput is used, a macro trim function is needed to remove extra space, otherwise, skip this step*/

%LET lastitem=%trim(&last);

/* To verify the macro variables are assigned, show all user-defined macro variables on the output window. If symput is used, you will see something like "last 30" and "lastitem 30." */

%put _user_;

/* transpose the list of itemid so that it is listed horizontally. By default the field names of the itemid are COL1-COLx */

proc transpose data=listitem out=listitem2; var itemid; run;

/* If the itemids start with a number, the following step is necessary. It will add a letter "Q" to every itemid to make it legal in SAS variable assignment. */

data listitem2; set listitem2;

length newitemid1-newitemid&last $15.

col1-col&last $15.;

array col(&last) $ col1-col&last;

array newitemid(&last) $ newitemid1-newitemid&last;

do i= 1 to &last;

newitemid(i) = "Q"||col(i);



/* output the new itemlist as a text file. If the real IDs do not start with a number, the preceding step is not necessary and you can write the output statement as "put col1 - col&last" */

data itemid2; set itemid2;

FILE "&path\newitemlist.txt" lrecl=35000;

put newitemid1 - newitem&last;


/* define the last position of the text file. In this case, each itemid has 5 digitals plus the letter "Q" (5+1=6). */

%LET lastpos=%eval(&lastitem * 6 + &lastitem - 1);

/* read the itemid list from text file back to SAS as a long string, and then put the string

into a macro variable */

data itemlist3; infile "&path\newitemlist.txt" lrecl=35000;

input itemidlist $1 - &lastpos;

call symput("itemidlist", itemidlist);


/* replace the old itemid list with the new list */

data finaloutput; set finaloutput;

array temp(&last) tempid1-tempid$last;

array newitemid(&last) &itemidlist;

do i = 1 to &last;

newitemid(i) = temp(i);


drop temp1-temp&last i;





Good luck! And happy SASing!


SAS tips contents

Computer write-ups contents

Simplified Navigation

Table of Contents

Search Engine