topic Re: Combining text and macro variables in table name in SAS Programming

Combining text and macro variables in table name

deleted_user — Fri, 03 Sep 2010 17:49:32 GMT

Hi everyone,

I'm having a problem with my code that seems trivial, but I've tried every permutation I can think of to no avail.

I have one dataset, which I want to break into 4 datasets with a where clause, and then add additional columns. When I explicitly name the columns, this works fine. However, my problem has been with the macro variables.

This is what I want to do (which doesn't work):

proc sql;
create table work.&data1.winter as
select distinct &data1..*,
(.....new columns....)
from &lib..&data1

lib and data1 are inputs. If I had "create table work.&data1" it would work, and if I had "create table work.winter" it would work; how can I concatenate a macro variable and text within the name of a table?

When I run it in SAS it tells me "Syntax error, expecting one of the following: (, AS, LIKE"

Thank you.

Re: Combining text and macro variables in table name

RickM — Fri, 03 Sep 2010 18:12:41 GMT

It would be more helpful to actually see all of the code from the log so we can see exactly where this error of yours is occuring. It doesn't help to put (.....new columns....) and have part of a SAS error separated from the code.

Re: Combining text and macro variables in table name

deleted_user — Fri, 03 Sep 2010 18:16:26 GMT

Sorry about that, it's just that it's a fairly large macro. This is everything preceding the error.

%macro pcamacro(indata, lib, all_deps, indvars =, indvars2 =, indvars3 =);
proc datasets lib=work kill;
quit;
run;
%let j=1;
%let data1 = %qscan(&indata, &j);
%do %while(&data1 NE);
data &data1;
set &lib..&data1;
;
%let l=1;
%let dep = %scan(&all_deps, &l);
%do %while(&dep NE);

proc sql;
create table work.&data1.winter as
select distinct &data1..*,
avgwind * (jan + feb + decem) as winteravgwind,
maxwind * (jan + feb + decem) as wintermaxwind,
log(striketotal+1) * (jan + feb + decem) as winterstriketotal,
log(maxstriketot+1) * (jan + feb + decem) as wintermaxstriketot,
log(raintotal) * (jan + feb + decem) as winterraintotal,
log(maxdailyrain) * (jan + feb + decem) as wintermaxdailyrain,
avghitemp * (jan + feb + decem) as winteravghitemp,
maxhi * (jan + feb + decem) as wintermaxhi,
avglotemp * (jan + feb + decem) as winteravglotemp,
minlo * (jan + feb + decem) as winterminlo,
pressure * (jan + feb + decem) as winterpressure
from &lib..&data1
where avgwind * (jan + feb + decem) > 0;

(same process to try to create spring, summer, fall)
quit;

Re: Combining text and macro variables in table name

deleted_user — Fri, 03 Sep 2010 18:29:32 GMT

I just heard back from an expert at my company and it looks like using this line instead fixes that problem:

%let data1 = %trim(%qscan(&indata, &j));

However, I'm still having a similar problem in another part of the code. I do a factor analysis, and ask it to loop through k:

%do k = 3 %to k = 5;
proc factor data=&data1.winter scree nfact=&k rotate=qmin score outstat=&data1.factwinter&k;
var winteravgwind wintermaxwind winterstriketotal wintermaxstriketot winterraintotal
wintermaxdailyrain winteravghitemp wintermaxhi winteravglotemp winterminlo winterpressure;
run;

It looks like it doesn't produce the output data set.

Re: Combining text and macro variables in table name

ArtC — Fri, 03 Sep 2010 19:35:54 GMT

For the %DO loop your syntax is off a bit. Try
[pre]%do k = 3 %to 5;
[/pre]

The syntax for the data set name e.g.
[pre] work.&data1.winter [/pre]
should be fine provided that &DATA1 resolves tothe first part of your data set name and does not contain trailing blanks. For instance if &DATA1 contains ALL your data set becomes:
[pre] work.ALLwinter [/pre]
but who wants to work all winter anyway.

Re: Combining text and macro variables in table name

deleted_user — Tue, 07 Sep 2010 12:10:10 GMT

Thanks!