rename variabile with an element of list by macro variable

[antosferry]

Hi,

I created a macro variable using proc sql ....select into....

The macro variable is for example:

%put &top10.; run;

'04050606', '94958693', '20494585'....

I want to rename a variable of a dataset with the first element of list macro variable that is a numeric code.

I used this:

proc sql noprint;
create table prova_new as select
drg_new, _c2_ as '%scan('&top10.',1,',')'n
from prova;
quit;

but it doesn't work:

ERROR 22-322: Expecting a name.  

 Thank you!

[Kurt_Bremser]

Your macro variable looks like it has been built to populate a list for the IN operator, so it is not suited to be used as variable names because of the quotes.

So your first step should be to build that macro variable without the quotes, and blanks as separators.

Then

'%scan('&top10.',1,',')'n

Two issues here: the single quotes prevent macro trigger resolution, so this translates to

'%scan(' &top10. ',1,' , ')' n

(blanks added to show the resulting breaks between separate strings).

Once you have built your list without quotes and commas, change the macro call to

c2_%scan(&top10.,1, )

Note that the use of the c2_ prefix makes using a name literal unnecessary.

[antosferry]

Excuse me,

i used this coding

data prova_new ;
set prova;

rename _c2_='%scan("&top10.",1,"',")'n;
run;

I also tried to create the variable with the result of scan and it work well. So the result is for example the x=04050606 without quote.

Could you write all code programming you should use?

Thank you

[Kurt_Bremser]

I have given you a full explanation and code that should work. First change the creation of the macro variable.

[PaigeMiller]

---

@antosferry wrote:

Excuse me,

i used this coding

data prova_new ;
set prova;

rename _c2_='%scan("&top10.",1,"',")'n;
run;

I also tried to create the variable with the result of scan and it work well. So the result is for example the x=04050606 without quote.

Could you write all code programming you should use?

---

As I said, you need to first create code that works without macro language, for one case. Show us code that works without macro language and then we can help you turn it into code that works with macro language. If you can't get it to work without macro language, then it will never work with macro language.

[PaigeMiller]

You have a number of issues here, and I strongly suggest you start by hard-coding a simple example using just the value 04050606 and get your SQL code to work without macro language. If you can't get that to work, then you will never get the macro version to work. So, please show us a simple example of this working properly without macro language. That's the starting point for getting this to work and the starting point for further discussions in this thread.

Also, the construct 

as '012345'n

does not work in SQL.

[Tom · Accepted solution]

You can use %PUT statements to test if you can generate the text you need.

645   %let top10='04050606', '94958693', '20494585';
646   %put %scan(%superq(top10),1,%str(,))n;
'04050606'n

Then you can use it to generate code:

create table prova_new as 
  select drg_new
       , _c2_ as %scan(%superq(top10),1,%str(,))n
  from prova
;

 Watch out for spaces in your list.  If there are spaces before the commas then that simple code will not work as then the N will not be right after the close quote.

647   %let top10='04050606' , '94958693' , '20494585';
648   %put %scan(%superq(top10),1,%str(,))n;
'04050606' n

[antosferry]

Thank you very much, it works well!!