Help using Base SAS procedures

Bind two coloum value into Macro Variable? Select into?

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Bind two coloum value into Macro Variable? Select into?

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


Accepted Solutions
Solution
‎10-12-2011 11:32 PM
PROC Star
Posts: 7,491

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

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.

View solution in original post


All Replies
Solution
‎10-12-2011 11:32 PM
PROC Star
Posts: 7,491

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

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.

Super User
Posts: 10,044

Bind two coloum value into Macro Variable? Select into?

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

Super User
Super User
Posts: 7,076

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

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 185 views
  • 6 likes
  • 4 in conversation