Thanks, I really like the solution and it works.  The only thing is that the variable names are actually long and sometimes are just long alphanumeric codes -- in this case , is there a short cut to creating the dataset orderedvars? I have all the variable names stored as a list in the macrovariabe &orderedvars2. I tried using the code below but it didn't run. Should I use call execute inside the data step --- what would be the syntax, if its advisable?   data orderedvars; input var $; cards; &orderedvars2; ; run;   It says in the error message: NOTE: Line generated by the macro variable "ORDEREDVARS2". 27 STD_fri STD_VEHICLE_SALES STD_PASSCAR_SALES STD_TRK_SALES STD_CPI_TRANSPORT STD_LF_PART_RATE STD_GASCPI_NSA STD_CANCPI _______ 180 27 ! STD_CANCORECPI STD_UNEMPLOY_RATE STD_BOND_RATE_10YR STD_CPI STD_CPI_CORE STD_RGDP STD_RGDP_USD STD_EXCH_CAND_USD 27 ! STD_EXCH_CAND_EUR ERROR 180-322: Statement is not valid or it is used out of proper order.   ... View more

I also tried with single quotes instead of double quotes. and get these errors--- not sure what they indicate-- here it looks like the %get_word macro's output does not get generated inside call execute: %macro test;   %let n = %sysfunc(countw(&orderedvars2) );   data _null_;   call execute ('proc expand data=STNDStandardized3 out=logvars1 method = none;    id date2; ');      %do i = 1 %to &n;        call execute ( 'convert %get_word(&orderedvars2,&i) = log_'||'%get_word(& orderedvars2,&i) / transformout=(log);');      %end;   call execute ( ';run;');   run; %mend; %test;   ERROR: More positional parameters found than defined. NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time):       real time           0.08 seconds       cpu time            0.09 seconds     NOTE: CALL EXECUTE generated line. 1   + proc expand data=STNDStandardized3 out=logvars1 method = none;  id date2; NOTE: Line generated by the CALL EXECUTE routine. 2   + convert = log_ / transformout=(log);       -------       180 ... View more

Thanks so much for your response. I tried both suggestions and still get errors... please do let me know if you have any idea what may be causing the problem! 1.) Replacing &&var&j. with "&&var&j." inside the macro %get_word but not using double-quotes in the macro call gives the error: NOTE 137-205: Line generated by the invoked macro "TEST". 3 call execute ( 'convert '|| %get_word(&orderedvars2,&i)||' = -- 22 3 ! log_'||%get_word(&orderedvars2,&i)||' / transformout=(log);'); ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, INPUT, PUT. ERROR: More positional parameters found than defined. 2.) Enclosing the macro call in double_quotes, but not changing the %get_word macro also gives an error: %macro test; %let n = %sysfunc(countw(&orderedvars2)); data _null_; call execute ('proc expand data=STNDStandardized3 out=logvars1 method = none; id date2; '); %do i = 1 %to &n; call execute ( 'convert '|| "%get_word(&orderedvars2,&i)"||' = log_'||"%get_word(&orderedvars2,&i)"||' / transformout=(log);'); %end; call execute ( ';run;'); run; %mend; %test; ERROR: More positional parameters found than defined. ##- Please type your reply above this line. Simple formatting, no attachments. -## ... View more

Thanks, I think using proc format is the way to go- didn't know about this option before. Thanks also for the nice explanation about %If and If statements! ... View more

Thanks very much for the detailed reply. It comes close to the problem I'm facing.  I am trying to create bins for different variables ("V") in a dataset ("A"), but each variable is handled seprately and produces an associated binned dataset of its own through %CreateNewData. My goal is to generate these different "binned" datasets corresponding to each variable(for  further analysis later on). I wanted to have a good structure to handle the many variables in my dataset, so I knew I needed to use macros - though I do agree I have complicated things quite a bit 😞     The macro %ProcessOrigData -which assigns the number of Bins and the individual break points for each variable- passes the dataset A and the current variable V to be binned to the macro %CreateNewData .  The number of bins "&Num" and the  exact break points "&&v&i" for each variable are different - they are stored as global macro variables currently inside %ProcessOrigData, though it is a complicated setup.   The macro  %CreateNewData bins the variable V according to the breakpoints "&&v&i" and creates a new data set which contains the Bin number ("rank" in my code ). I would really like to figure out a neater structurefor all this !    In your code, I think I would just use your %DO loop as the outer loop. So it would look like the code below. I'm going to use "&&BrPt&j" instead of  "&&v&i"  and "&numBreaks" instead of "&Num" for clarity. %macro CreateNewData(data= ,out= ,var= /*space delimited list of variables*/ ); %local i vari; data &out; set &data; %*loop over the list of variables, generating an if/else block for each variable; %do i=1 %to %sysfunc(countw(&var,%str( ))); %let vari=%scan(&var,&i,%str( )); /** Loop over the number of Bins for the current variable. Both &numBreaks and &&BrPt&j are global. **/ %do j=1 %to &numBreaks; high=&&BrPt&j.; /** Missing values go in Bin 0 **/ if missing(&vari) then do; rank = 0; end; else do; if (&vari < high and rank = -1) then rank = &j.; end; %end; /** Values greater than the highest break point are in the highest bin **/ max = &&BrPt&numBreaks.;maxBin = %eval(&numBreaks+1); if ((&vari >= max) and rank = -1) then rank =maxBin; %end; run; %mend CreateNewData; options mprint; %CreateNewData(data=sashelp. class,out=want,var=height weight)   I'm including a skeleton of the macro %ProcessOrigData below, along with some code I found from the DO loop blog to generate dummy data. I am treating the macro variables &numBreaks and all the possible break points I can have &BrPt1,...etc as global macro variables.   /** Get dummy data **/ /* SAS macro that duplicates the Excel RANDBETWEEN function */ %macro RandBetween(min, max); (&min + floor((1+&max-&min)*rand("uniform"))) %mend; data test; do i = 1 to 100; height = %RandBetween(100, 200); weight = %RandBetween(40, 100); output; end; run;   /**  Code for the macro ProcessOrigData **/ /** I just initialize the maximum possible break points I cna have for any variable to zero. This isn't really optimal..... **/ /** I also have a lot of variables in the real problem ...***/   %let numBreaks =0; %let BrPt1 =0; %let BrPt2 =0; %macro processOrig(inputVar); if &inputVar = height then do; &numBreaks = 1; &BrPt1 = 160; end; else do; &numBreaks = 2; &BrPt1 = 50;&BrPt2 = 70; end;   Please do give your comments on what would be an efficient way to tackle this binning problem... Thanks for your help! ... View more

Thanks for the reply.  What I ended up doing was rename the variable &V to the generic Var1 . This works for my purposes since I only need to pass in one variable along with my dataset A each time. I have also removed the %IF --- now, there are only normal IF statements inside the data step.   however, you said there should be no macro statements inside the Data step. I still have the %DO loop which runs fine. Please see below. So it looks like macro functions can be processed inside Data steps, maybe just not inside an If function? Is that right?   %macro CreateNewData(A, V); data outdata.; set &A; %do i=1 %to &Num;         high=&&v&i.;         if missing(Var1) then         do;             rank = 0;        end;      else do;              if (Var1 < high and rank = -1) then rank = &i.;      end; %end; run; ... View more

Sorry , that was a typo in my post -- I do actually call %createNewData(A,&V) not V. I still get errors. I would like to be able to use the name of the variable inside my data step within %createNewData. That is, suppose &V = Age.  I want to say if ( Age < high) then do....   At the moment, SAS does not allow me to execute that IF statement with or without the % sign inside the data step.   ... View more