Bind two coloum value into Macro Variable? Select into?

[Liangck]

Hi all,

     I am here again, because of having problem.

     My problem senario is like this...

     I haved a table call etllib.TempStoreDistinctValue.Inside this table, provided with two coloum name WarehouseCode, ItemCode

     What i wanted to do is

      i wanted to loop through this table by getting to value and pass the value to a Macro Variable.

     Before this, i tried to bind only a coloum into a Macro variable at proc sql

     this is how i done it.

proc sql noprint;
     select count(BranchCode)
          into :n
          from etllib.TblBranchLookUp;
     select BranchCode into:BranchCode1 - :BranchCode%left(&n)
          from etllib.TblBranchLookUp;
quit;

     This is for one coloum only, how about two coloum?

     i tried this code.

proc sql noprint;
     select count(Warehouse)
      into :Whs
      from etllib.TempStoreDistinctINVNMV;
     select Warehouse,ItemCode into :Warehouse1 - :Warehouse%left(&Whs),ItemCode1 - :ItemCode%left(&Whs)
      from etllib.TempStoreDistinctINVNMV; 
quit;

     Its seem got bit problem, but dont know where goes wrong that error code is expecting a :. near ItemCode1.

     Anthing goes wrong ?

     Or have other better method way?

     Thank you,Apperciate your help.

     Please dont hensitate to  voice out my error or mistake that i done.Learn from mistake is a good thing.Thank you.

Liang

[art297 · Accepted solution]

Your error message was correct: You are missing a colon in front of  ItemCode1

Here is a version of your corrected code using sashelp.class as the data:

proc sql noprint;

    select count(name)

      into :Whs

        from sashelp.class;

         select Name,age

          into :Warehouse1 - :Warehouse%left(&Whs),

                :ItemCode1 - :ItemCode%left(&Whs)

             from sashelp.class

  ;

quit;

I always find it easier to identify errors when the code is properly indented.

[Ksharp]

Maybe you want this:

proc sql feedback;
 select name,age from sashelp.class;
 select cats(name,age)
     into : bind1 - : bind&sqlobs.
   from sashelp.class;
quit;

%put _user_;

Ksharp

[Tom]

You do not need to count in advance when selecting values into what some people call a macro "array".  Just set the upperbound to some value that is larger than the maximum you could find or process.

proc sql noprint;
  select Warehouse

       , ItemCode

    into :Warehouse1 - :Warehouse99999

       , :ItemCode1 - :ItemCode99999
    from etllib.TempStoreDistinctINVNMV

  ;

  %let Whs=&sqlobs;
quit;