topic Bind two coloum value into Macro Variable? Select into? in SAS Procedures

Bind two coloum value into Macro Variable? Select into?

Liangck — Thu, 13 Oct 2011 02:18:21 GMT

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

Re: Bind two coloum value into Macro Variable? Select into?

art297 — Thu, 13 Oct 2011 03:32:27 GMT

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.

Bind two coloum value into Macro Variable? Select into?

Ksharp — Thu, 13 Oct 2011 04:56:04 GMT

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

Re: Bind two coloum value into Macro Variable? Select into?

Tom — Thu, 13 Oct 2011 13:51:05 GMT

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;