DATA Step, Macro, Functions and more

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

Reply
New Contributor
Posts: 2

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

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

-------

22

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 =

ES

NL

FR

DE

UK

IT

IE

CZ;

 

%let exers_year=2016;

%let Prev_year=2014;

 

 

 

libname misc "\DIR\misc\";

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

Valued Guide
Posts: 797

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

Does this fix it?

 

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

    set BUdata.&dsn;

 

MK

Super User
Posts: 5,081

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

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.

New Contributor
Posts: 2

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

Thanks a lot, with uniqote it works for me.

Trusted Advisor
Posts: 1,374

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

Try use:

 

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

Ask a Question
Discussion stats
  • 4 replies
  • 175 views
  • 3 likes
  • 4 in conversation