topic Re: calling a data base with a name composed of two macro variables in SAS Programming

calling a data base with a name composed of two macro variables

Panianka — Thu, 01 Dec 2016 16:16:57 GMT

Dear all,

I am stuck with rather simple task: I have several databases named country_year stored in respective directories by country and I have to open them to extract some data.

So I have a macro scrolling the list of countries, creating a library and treating the data base I need.

Here is the code　

%macro buyerslist(list, year, out);

**********************************************************************

First I scroll the list and at every step get a country from the list

**********************************************************************

%let n=%sysfunc(countw(&list.));

%do i=1 %to &n;

%let bu=%qscan(&list.,&i.,%str( ));

*********************************************************************************************

I creat the library for respective country and call a data base named country_year

***********************************************************************************************

libname BUdata "DIRECTORY\&bu.\";

%put Budata.&bu._&year.;

data &out..&bu._buyers (keep = buyers );

set BUdata.&bu._&year.;

run;

*******************************************************

This part somehow does not work.

%put Budata.&bu._&year.; gives a correct result (EX &bu=ES , &year=2014)

Budata.ES_2014

But set BUdata.&bu._&year. doesn't work correctly, it doesn't read "ES_2014" as one but cuts it in two peaces "Budata.ES" and "_2014"

ERROR: File BUDATA.ES.DATA does not exist.

ERROR: File WORK._2014.DATA does not exist.

Ofcause set BUdata.&bu_&year. wouldn't work since SAS will read it as a non existing macro variable

1 BUdata.&bu_2014

-------

201

WARNING: Apparent symbolic reference BU_2014 not resolved.

What should I do to call a data base in a macro with a name composed of two macro variables?

*******************************************************

run;

libname BUdata clear;

%end;

%mend buyerslist;

%let bulist =

CZ;

%let exers_year=2016;

%let Prev_year=2014;

libname misc "\DIR\misc\";

%buyerslist(&bulist., misc, &Prev_year.);

Re: calling a data base with a name composed of two macro variables

mkeintz — Thu, 01 Dec 2016 16:35:02 GMT

Does this fix it?

%let dsn=%trim(&bu)_&year;

set BUdata.&dsn;

Re: calling a data base with a name composed of two macro variables

Astounding — Thu, 01 Dec 2016 16:37:01 GMT

You're right. The program isn't behaving properly. Experiments to try ...

First, try retyping the BU list:

%let bulist = ES NL FR DE UK IT IE CZ;

It's conceivable that the editor used to construct the original %LET statement introduced characters that SAS isn't parsing correctly (carriage returns, line feeds).

If that doesn't work, try applying %UNQUOTE:

set %unquote(BUdata.&bu._&year.);

That may overcome the way that SAS is treating the pieces as separate strings.

Re: calling a data base with a name composed of two macro variables

Shmuel — Thu, 01 Dec 2016 16:42:49 GMT

Try use:

set %sysfunc(compress(BUdata.&bu._&year.));

Re: calling a data base with a name composed of two macro variables

Panianka — Fri, 02 Dec 2016 15:16:54 GMT

Thanks a lot, with uniqote it works for me.