Help with array in macro loop and assigning variables

Reply
New Contributor
Posts: 3

Help with array in macro loop and assigning variables

Hi,

No douobt this is an easy question and I am missing something simple but any help would be appreciated!

I have the following code as per below. Essentially I am creating an Array (column1 to column10) in the example below which I use to store the names of 10 particular variables in a dataset. That is obviously the SQL part of my macro below. All is good with this. I then essentially want to use this information to dynamically create 10 'mirror' variables in my dataset but the mirror variables will change the name by adding on the word Character at the end of the existing name and, as you can see, they are character versions of the numeric variables I currently have. I am trying to do this in the line:

&column&count.Character  = put(&&column&count, 6.1);

All works with the below (the loop is good etc) except for that one line above. Clearly I am gettingn something mixed up between compile and run-time as the actual values held in the column1-column10 are not gettingused. Can anyone please offer me a small bit of help?!

The log by the way is giving me:

WARNING: Apparent symbolic reference COLUMN not resolved.

NOTE: Line generated by the invoked macro "PROCESSDATA".

35             &column&count.Char  = put(&column&count, 6.1);

               _

               180

WARNING: Apparent symbolic reference COLUMN not resolved.

WARNING: Apparent symbolic reference COLUMN not resolved.

NOTE: Line generated by the invoked macro "PROCESSDATA".

35             &column&count.Char  = put(&column&count, 6.1);

             

%MACRO ProcessData;


PROC SQL;
SELECT name INTO :column1 - :column&quarterCount FROM dictionary.columns
WHERE LIBNAME = 'WORK' and memname = 'TABLE2_FINAL' and NAME like 'SumPersons%';
QUIT;

%let count=1;

data Table2_Finalxx;
set Table2_Final;
%Do %until (&count=%eval(&quarterCount+1));
   &column&count.Character  = put(&&column&count, 6.1);

%let count=%eval(&count+1);
%end;
run;
%mend;

%ProcessData;

Super Contributor
Posts: 282

Re: Help with array in macro loop and assigning variables

Posted in reply to brianring

Hi,

If I've understood what you're trying to do, then I would suggest removing the initial ampersand so that:

&column&count.Character  = put(&&column&count, 6.1);


becomes:


column&count.Character  = put(&&column&count, 6.1);


as I understand this is supposed to be a data step variable.


Regards,

Amir.

PROC Star
Posts: 7,492

Re: Help with array in macro loop and assigning variables

Posted in reply to brianring

I think you are just missing one additional period in the macro variable to the left of the assignment statement.  I've modified your code a bit because you used a macro variable that you hadn't provided in your example, as well as I tested it on sashelp.class:

data TABLE2_FINAL;

  set sashelp.class (rename=(height=SumPersonsHeight

                             weight=SumPersonsWeight

                             age=SumPersonsAge));

run;

%MACRO ProcessData;

PROC SQL noprint;

  SELECT name into :junk

    FROM dictionary.columns

      WHERE LIBNAME = 'WORK' and memname = 'TABLE2_FINAL' and NAME like 'SumPersons%'

  ;

%let numrecs=&sqlobs.;

  SELECT name INTO :column1 - :column&numrecs

    FROM dictionary.columns

     WHERE LIBNAME = 'WORK' and memname = 'TABLE2_FINAL' and NAME like 'SumPersons%'

  ;

QUIT;

data Table2_Finalxx;

  set Table2_Final;

  %Do count=1 %to &numrecs.;

    &&column&count..Character  = put(&&column&count, 6.1);

  %end;

run;

%mend;

%ProcessData

Super User
Posts: 19,878

Re: Help with array in macro loop and assigning variables

Posted in reply to brianring

I think this does similar to what you're asking for, but a bit easier to follow perhaps? Less macro coding at least, but probably not required.

%let append=new;

PROC SQL noprint;

    *add the variable in;

    create table names as

    SELECT name, catx("_", name,"&append") as name_&append FROM dictionary.columns

    WHERE LIBNAME = 'SASHELP' and memname = 'CLASS';

    *count how many variables;

    select count(*) into :count_vars from names;

    *Create old and new macro variables;

    SELECT name INTO Smiley Surprisedriginal separated by " " FROM names;

    SELECT name_&append INTO :appended separated by " " FROM names;

QUIT;

%put &original;

%put &appended;

data test;

    set sashelp.class;

    array original(*) &original;

    array new(*) &appended;

    do i=1 to dim(original);

        original(i)=put(new(i), 6.1);

    end;

run;

New Contributor
Posts: 3

Re: Help with array in macro loop and assigning variables

Posted in reply to brianring

Hi,

First thanks to everyone for their suggestions. I am not in work (am in Europe so night time here!) so cannot try to build on the suggestions offered. But just to clarify what I am trying to do in actual Base SAS code.

This quarter I might have a dataset that has the following variables:

classification1   classification2   SumPersonsQtr12010   SumPersonsQtr22010   SumPersonsQtr32010 etc

Each of the SumPersons variables is numeric. I want to create a 'mirror' variable which is character. So in actual SAS what I want for this quarter is:

Data Table2_Finalxx;

     set Table2_Final;


     SumPersonsQtr12010Character = put(SumPersonsQtr12010,6.1);

     SumPersonsQtr22010Character = put(SumPersonsQtr22010,6.1);

     SumPersonsQtr32010Character = put(SumPersonsQtr32010,6.1);


run;



However, my program that builds the dataset Table2_Final is dynamic so for example next year I will have:

classification1   classification2   SumPersonsQtr12011   SumPersonsQtr2201 SumPersonsQtr32011

and then my next block of code next year would need to be:

Data Table2_Finalxx;

     set Table2_Final;

     SumPersonsQtr12011Character = put(SumPersonsQtr12011,6.1);

     SumPersonsQtr22011Character = put(SumPersonsQtr22011,6.1);

     SumPersonsQtr32011Character = put(SumPersonsQtr32011,6.1);

run;



The way I had tried to build it was to put the names of the SumPersons variables into the array:

So I get:

&column1 = SumPersonsQtr12010

&column2 = SumPersonsQtr22010

&column3 = SumPersonsQtr32010


I was then trying to use the &column&count statement to try and create the lines of code within the loop to mimic that example static code above- i.e. translate:

%Do %until (&count=%eval(&quarterCount+1));

   &column&count.Character  = put(&&column&count, 6.1);

to run:

SumPersonsQtr12010Character = put(SumPersonsQtr12010,6.1);

To be honest its not a huge difference whether I can do this in a macro or some other way - but I obviously have something mixed up!!!

Super Contributor
Posts: 282

Re: Help with array in macro loop and assigning variables

Posted in reply to brianring

Hi,

Looks like my last post (Sep 12, 2012 1:48 PM) was wrong as it would have generated, for example:

column1Character

Instead of removing the ampersand I'd now suggest the complete opposite, like Art said, adding an extra one, as well as an extra required period, so:

&column&count.Character  = put(&&column&count, 6.1);


becomes:


&&column&count..Character  = put(&&column&count, 6.1);

So that you generate, for example:

SumPersonsQtr12010Character

Regards,

Amir.

Ask a Question
Discussion stats
  • 5 replies
  • 469 views
  • 0 likes
  • 4 in conversation