The macro gives an error, despite the fact that there сolnames only 26 values ​​in the сolnames .

The macro task is to create k tables with 3 columns: End_of_the_period , Period , <the name of the 3 columns is taken from colnames> from the Month table.

 %Macro TablesEc2 ; proc sql; %do k=3 %to 5; create table intermediate&k. as select End_of_the_period, Period, %scan(&colnames,&k.) From Month; %end ;quit; %mend; %TablesEc2

UPDATE: The problem may be in the values ​​of сolnames , which may contain letters and numbers. Like Semestr_1 , Semestr_2

  • judging by the syntax, your question is specific to some kind of DBMS. Add a tag to the question - 4per
  • Hello. do you have colnames ? In your case, the % scan macro function is not as you want) - Sanek Zhitnik

2 answers 2

I will give an example of code for the standard CARS (page 23) from the SASHELP library:

 %Macro TablesEc2 ; //Объявляем макро переменную %let macromake=; //Создаем датасет с перечнем всех производителей proc sql; create table Makers as select distinct Make from sashelp.cars; quit; //Добавляем к датасету колонку с идентификаторами data Makers; set Makers; rownum=_n_; run; proc sql; %do k=3 %to 5; //SQL запросом присваиваем макропеременной значение из датасета всех производителей select Make into :macromake from Makers where rownum = &k.; //Теперь создаем таблицу в цикле create table cars&k. as //Выбирая каждый раз столбец с новым названием select &macromake From sashelp.cars; %end quit; %mend; %TablesEc2

However , this test example will give you an error for the reason that there are no columns with names of manufacturers in the CARS dataset. This can be fixed by making your dataset. Here I just set an example of how this should work.

It may be easier for you to initially select a column in a SQL query by some condition and only then, in a separate data step, rename it in all created datasets (macrocycle to help) .

Hope that helped you. If there are additional questions - ask in the comments.

     %Macro TablesEc2 ; /*//Объявляем макро переменную*/ %let macromake=; /*Создан заранее датасет с именами Semestr_1, Semestr_2 в первом столбце (vname) и соответствующими номенами 1 2 3 4 во втором столбце (n)*/ proc sql; %do k=3 %to 5; /*//SQL запросом присваиваем макропеременной значение из датасета всех производителей*/ select vname into :macromake from colnames where n = &k.; /* //Теперь создаем таблицу в цикле */ create table cars&k. as /* //Выбирая каждый раз столбец с новым названием*/ select &macromake From month; %end quit; %mend; %TablesEc2