BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
5 REPLIES 5
RickM
Fluorite | Level 6
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.
deleted_user
Not applicable
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;
deleted_user
Not applicable
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.
ArtC
Rhodochrosite | Level 12
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 8065 views
  • 0 likes
  • 3 in conversation