Create unique variable names in macro

[Morten]

Hi

I have a functioning macro running through daily datasets. The macro itself works as it should and I get the outputs I want.
I would however like for SAS to save the files it generates as OUTPUTAdate OUTPUTBdate etc.

I can do so using this:

data&year&mon2&day2

and then substitute data with whichever name I would like the datasets I create along the way to have.
I just can't seem to get it to work properly in a proc sql statement. One workaround is using proc sort instead, but that takes a substantially longer time to run and is my last resort.

proc sql;
create table book&year&mon2&day2 as
select * 
from data&year&mon2&day2, base&year&mon2&day2
where data&year&mon2&day2.ref = base&year&mon2&day2.ref;
quit;

Is there a solution to this?

Thanks in advance

Morten

 

[Janus]

What error are you getting? My guess is that in your where clause you are ending with "&day.". The period tells SAS it has reached the end of the macro. I would guess that if you put two periods -- "&day..ref" -- you would be fine. Or, just use table aliases (it is much neater anyway).

 

[Morten]

You were right about the extra . to create the filename. However, now I am getting an ERROR: BY variables are not properly sorted on data set WORK.DATA20100209.
And warnings that the variables already exist. I got this before, but it created the data I needed so I ignored the warnings. It is because of the

select *

step in proc sql. Is there any way to get around this?

 

[Janus]

If you are using proc sql, datasets do not need to be sorted. Is this error occurring b/c you are using a merge in a data step?

Do you need all of the variables from both datasets? Are they all unique (aside from? You have several options depending on the answers to those questions. You can use a table alias and the * command to grab all the variables from one dataset and specify specifics from the other.

Or, if they only have "ref" in common, rename "ref" in one of the datasets prior to joining, as proc sql does not require join variables to have the same name.

 

[Morten]

That makes sense. I didn't know the background of the proc sql well enough. Thanks for the info