Macro Processing

Reply
Contributor
Posts: 74

Macro Processing

I need some assistance. I am new to macro processing. 

What I want to do is create a macro that can be used for evaluating errors on records.

I have a table that looks like this:

Data qgrid;

Input strategy: $6. queue:$2.;

datalines;

MAY EX

MAY G2

MAY XR

MAY J2

MAY XX

MAY G8

MAY GR

MAY X2

JUNE EX

JUNE G2

JUNE XR

JUNE J2

JUNE XX

JUNE G8

JUNE GR

JUNE X2

JUNE MA

JACX EX

JACX G2

JACX XR

JACX J2

JACX XX

;

run;

First, I need the strategy and queue variables from this table to be macro variables so that I can pull the values I need to look for in the table from the same variables in the data record.  (at least this is what I think I need)

Second, the qgrid table represents a queue hierarchy and any queue above the one I select would resolve to a non error.  Anything below would be an error.

So, if I am looking for strategy JUNE with a queue of XX then any queue value above XX (EX G2 XR J2) and XX would all be allowed so would not be an error.  Any queue value below XX (G8 GR X2 MA) would all be an error since they are lower in the queue hierarchy. 

I would need to take the strategy value from the data record and have that value be used to evaluate the matching strategy from the queue hierarchy table.

I know I need to assign the strategy value from the data record to a macro variable.

I also need to write a macro to read each record and evaluate if the queue assignment on the record is an error or non error based on the hierarchy.

I hope this makes sense.

Any assistance will be greatly appreciated.

Thank you,

Cindy

Super User
Posts: 11,121

Re: Macro Processing

Some examples of the other input data and the expected output data. I would say that you actually mean "occurring earlier in the data set than XX" instead of above.

Does the order of your queue variables change in any months, other than possibly not occurring? For example is G8 always after XX whenever it occurs?

It is very likely that macros may not be needed.

Contributor
Posts: 74

Re: Macro Processing

Hi Ballardw,

I am not understanding what you are saying above.

this is from an email I received...

Some examples of the other input data and the expected output data. I would say that you actually mean "occurring earlier in the data set than XX" instead of above.   -- correct

Does the order of your queue variables change in any months, other than possibly not occurring? For example is G8 always after XX whenever it occurs?  --   I just made up the strategy/queue values, my actual table has over 600 records.  The queue value within the same strategy have to stay in the order listed. 

It is very likely that macros may not be needed.

This needs to be dynamic.  I have to take the strategy value from the record and compare to the queue hierarchy for that same strategy in the table.  I thought that assigning a macro variable was the best way to pass that information, but I don't know.  I have been working all day trying to figure this out and am not getting very far.  So far I have been able to assign a counter to the queue values for each strategy.  Then I was hoping to utilize the numeric to show error vs non error but cannot figure that out either unless I hard code everything and I cannot do that in this report.  That is why I wanted to have the qgrid table and be able to evaluate from there.  I hope this makes sense. 

I an working on only one queue evaluation, but this report is going to grow to include several hundred queue/strategy combinations.

thanks,

Super User
Posts: 19,098

Re: Macro Processing

Show us some output that would be expected, otherwise it's hard to follow the logic and easy to misstate logic.

I doubt you need a macro, perhaps a join maybe adding an ordering variable.

Contributor
Posts: 74

Re: Macro Processing

I will start with a dataset of records that meet specific criteria.

each one of those records needs to be compared to the queue hierarchy.  The correct hierarchy in the qgrid table will be determined by the strategy variable in the record.  So the error evaluation would look at the queue hierarchy for the specific strategy, if the queue assignment on the record is the same as the one I am checking (I will hardcode) or one of the queue values above the one hardcoded in the qgrid table then the record will result in a non error, else the record will be an error in the error report.

I have written code to do what I want if I hardcode all the values, but this needs to be dynamic.  I cannot hardcode all the values.

Thank you,

Super User
Posts: 5,362

Re: Macro Processing

Not having time to spell it all out, here's the approach I would use. 

First, transpose the queue data, obtaining something along these lines.

Variable names = strategy, queue1, queue2, ..., queueN

PROC TRANSPOSE does this fairly easily and dynamically ... you don't have to specify how many variables to create.  A sample observation would be:

MAY EX G2 XR J2 XX G8 GR X2

Then sort and merge with your other data set by STRATEGY.

Finally, set up an array of the QUEUE# variables and compare the array elements to the value you find in your other data set.

No macros involved.  The DATA step can tell you have many variables you have by coding along these lines:

array q {*} queue:;

do i=1 to dim(q);

Good luck.

Super User
Posts: 11,121

Re: Macro Processing

If, which I know is a big if, the "order" of the queue values doesn't change then this may be quite doable with a format that assigns a numeric value to each queue value. Then you can write comparisons involving the transformed values and greater or less make sense in programming logic easier than "before" or "after".

Contributor
Posts: 74

Re: Macro Processing

I agree, and I have figured out how to assign a number to each queue within the each strategy hierarchy.  And I can evaluate for errors using >= is non error, else error.  But I am still hardcoding all the values in the error logic.  I need to use the strategy value of the data record to identify which strategy/queue hierarchy to evaluate, then find the specific queue within the hierarchy and get the numeric value assigned to the queue and then use the number value in the error check. 

My thought was to take the value of the strategy variable in the data record, use it to create a macro variable that could be used in a macro to read the qgrid hierarchy and find the queue we are testing for.  Take the numeric value assigned to that queue and create another macro variable to use in the error logic.  This is turning out to be a lot harder than I thought it would be. 

thanks,

Super User
Posts: 19,098

Re: Macro Processing

I don't think you're explaining it clearly.  Post at least 2 scenarios.

Contributor
Posts: 74

Re: Macro Processing

hopefull this explains better what I am trying to do.

I have one table that contains a hierarchy grid of the strategy/queue information. Each q within the strategy is assigned a numeric in ascending order.

Looks like this: (this is just an example my actual table has 9 strategies with approximately 100 queue values) not all strategies will contain all queues or have them in the same order.

Strat1 QueA 1

Strat1 QueB 2

Strat1 QueC 3

Strat2 QueA 1

Strat2 QueB 2

Strat2 QueC 3

Strat2 QueD 4

Strat3 QueA 1

Strat3 QueB 2

Strat4 QueA 1

Strat4 QueB 2

Strat4 QueC 1

I have a second table of data records that need to be evaluated for error against the hierarchy grid. In order to identify the correct strategy/queue hierarchy I need to use the strategy/queue values from the data record.

Data records look like this:

Id_num strategy queue var1 var2 var 3….

12345 Strat1 QueB var1 var2 var 3….

24589 Strat3 QueA var1 var2 var 3….

35984 Strat4 QueC var1 var2 var 3….

45695 Strat1 QueA var1 var2 var 3….

I am evaluating for QueB.

So I have to evaluate each data record to see if it has a QueB value in the queue field on the data record OR has a queue value that is before QueB in the hierarchy for the specific strategy/queue hierarchy in the table based on the strategy value in the strategy field on the data record.

How do I use the strategy/queue values from the data record to determine which strategy/queue hierarchy in the grid to evaluate against?

once I determine if the record is in error then I would populate a new variable lets say ERR = 1 for error ERR = 0 for non error.  something like that, so each record in the second table will be assigned an error or non error.

thanks,

Super User
Super User
Posts: 7,711

Re: Macro Processing

Just merge your Que value to your other data, then array over the vars to see if one is the value or above - note in this example I am assuming that having a 2 indicates there is 1 or 2 as valid (i.e. if they are not sequential the logic would need to change slightly):

data que;

  strat="Strat1"; que="QueA"; val=1; output;

  strat="Strat1"; que="QueB"; val=2; output;

  strat="Strat1"; que="QueC"; val=3; output;

run;

data hgrid;

  id_num=12345; strat="Strat1"; que="QueB"; var1=1; var2=1; var3=1; output;

  id_num=23456; strat="Strat1"; que="QueB"; var1=1; var2=2; var3=1; output;

run;

proc sql;

  create table CHECKED as

  select  A.*,

          B.VAL

  from    HGRID A

  left join (select STRAT,QUE,MAX(VAL) as VAL from QUE group by STRAT,QUE) B

  on      A.STRAT=B.STRAT

  and     A.QUE=B.QUE;

quit;

data checked;

  set checked;

  array var{*} _NUMERIC_;

  error=0;

  do i=1 to dim(var);

    if var{i}>=val then error=1;

  end;

run;

Contributor
Posts: 74

Re: Macro Processing

Hi RW9,

I have been able to use the proc sql to create the VAL variable which assigns a the numeric value from the grid table to the que value on the data record.

The error check is specific for the que I am error checking for.  So lets say I am looking for the QueB because the record meets the criteria to be in QueB.  If the que value on the record is not QueB or any queue value before QueB in the hierarchy then it would be an error.

I don't know how to pull the specific VAL value from the hierarchy table (i renamed VAL to qval so both tables don't have the same variable) to get the numeric value of QueB from within the strategy/queue hierarchy to compare the VAL value on the data record.  If I could extract that value then I could compare the VAL value on the data record and if the numeric value on the data record is greater than the hierarchy table value for that strategy/queue combination then it would be an error.

if the strat1/queb qval  is 2 on the hierarchy and the record has strat1/quec so val would be 3 then I need to compare them... how could I just add the qval value from the hierarchy table to the data record to compare against?   then I could say if VAL > qval then error = 1, else error = 0.

Super User
Super User
Posts: 7,711

Re: Macro Processing

Ah, so if it was QueC, then it would need to be the value of QueC, or QueB, or QueA?  If so then the logic still follows, as if the value in question is less than or equal to 2, for instance, then its in the list of QueA and QueB.

I am leaving now, but if you post a datastep with some test data, plus result I can have a look. 

Super User
Super User
Posts: 6,844

Re: Macro Processing

Still not following this, but let's use 's proposal to rotate the "queue" up into multiple columns.  Now we can use the WHICHC() function to locate the current and target states in the list.

data qgrid;

length strategy $6 state1-state20 $2 ;

infile cards truncover ;

input strategy state1-state20 ;

datalines;

JACX EX G2 XR J2 XX

JUNE EX G2 XR J2 XX G8 GR X2 MA

MAY EX G2 XR J2 XX G8 GR X2

;;;;

%let target=XX ;

data mydata ;

  length id 8 strategy $6 state $2 expected 8;

  input id strategy state expected;

cards;

6 JACX XR 1

7 JACX ZZ 0

4 JUNE EX 1

5 JUNE X2 0

1 MAY G2  1

2 MAY GR  0

3 MAY XX  1

;;;;

data want ;

  merge mydata qgrid ;

  by strategy ;

  array q state1-state20 ;

  status = ( 0< whichc(state,of q(*)) <= whichc("&target",of q(*))) ;

  put (id strategy state expected status) (=);

run;

Super User
Posts: 11,121

Re: Macro Processing

In general BEFORE doing anything with a macro or macro logic you need to have something that works for at least several cases without a macro. All macro's do at heart is generate SAS code. Which code to generate is the issue. Once you know what the code looks like in general then you may want to write macros to perform the same thing to different data sets and variables.

Ask a Question
Discussion stats
  • 15 replies
  • 462 views
  • 1 like
  • 6 in conversation