BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Liangck
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
art297
Opal | Level 21

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
Super User

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
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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