&sysmaxlong contains the largest integer number SAS can handle. &sqlobs will tell you how many values were actually returned, and thus how many macro variables were created.

proc sql; select name into :name1 - :name&sysmaxlong from one ; quit;

%let num_names = &sqlobs;

What you was very close to working. However, the &nobs value had a bunch of leading spaces that needed to be dealt with before :name&nobs would have worked. eg. %let nobs = &nobs after creating it. It also appears that upcase(libname) and upcase(memname) have a HORRIBLE effect on execution time. dictionary.tables libname / memname entries are always uppercase, no need to use the functions.

-----Original Message----- From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Kevin Zhang Sent: 2007, April, 07 10:11 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Creating multiple Macro Varibles in SQL

In data set "one", there is a variable called "name". I'd like to assign each value(row) of the "name" into a Macro variable. There're tons of ways to do it, but I am just wondering how can achieve this task within SQL when the size of the data set is unknown?

My code below isn't working

Thank you.

Kevin

proc sql; select nobs into:nobs from dictionary.tables where upcase(libname)="WORK" and upcase(memname)="ONE"; quit;

proc sql; select name into: name1-:name&nobs. from one; quit;

________________________________________________________________________ ____________ Be a PS3 game guru. Get your game face on with the latest PS3 news and previews at Yahoo! Games. http://videogames.yahoo.com/platform?platform=120121 _______________________________________________________________________

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par un autre moyen.