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

Guys, I am doing a concatenation reading data one observation at a time. So my data and code is as follows:

data have;

input @1 label1 $ @3 label2 $ @5 label3 $1. @7 condition $;

cards;

a d    p code1

a d    p code7

b e    q code2

b e    q code3

c p      code4

d a    r code5

d a    r code6

;

run;

options nomlogic ;

%macro rules (table_name);

/* open the table*/

%let table_id=%sysfunc(open(&table_name,i));

/* fetch the first record */

%let rc=%sysfunc(fetch(&table_id));

%if &rc ne 0 %then

%do;

    %put something is wrong ...;

%end;

%else

%do;

    /* read data for first record */

    %let label1=%sysfunc(getvarc(&table_id,

                   %sysfunc(varnum(&table_id,label1))));

    %let label2=%sysfunc(getvarc(&table_id,

                   %sysfunc(varnum(&table_id,label2))));

    %let label3=%sysfunc(getvarc(&table_id,

                   %sysfunc(varnum(&table_id,label3))));

    %let condition=%sysfunc(getvarc(&table_id,

                   %sysfunc(varnum(&table_id,condition))));

    /* Save values and iterate again */

    %let prev_label1=&label1;

    %let prev_label2=&label2;

    %let prev_label3=&label3;

    %let prev_condition=&condition;

/* Read remaining records */

    %let rc=%sysfunc(fetch(&table_id));

    %do %while (&rc eq 0);

       

    %let label1=%sysfunc(getvarc(&table_id,

                   %sysfunc(varnum(&table_id,label1))));

    %let label2=%sysfunc(getvarc(&table_id,

                   %sysfunc(varnum(&table_id,label2))));

    %let label3=%sysfunc(getvarc(&table_id,

                   %sysfunc(varnum(&table_id,label3))));

    %let condition=%sysfunc(getvarc(&table_id,

                   %sysfunc(varnum(&table_id,condition))));

    %if (&prev_label1 eq &label1)and (&prev_label2 eq &label2) and (&prev_label3 eq &label3) %then

    %do;

    %let new_condition=case when &prev_condition or (&condition);

    %put new_condition is &new_condition;

    %end;

    %else %do;

    %let prev_label1=&label1;

    %let prev_label2=&label2;

    %let prev_label3=&label3;

    %let prev_condition=&condition;

    %let new_condition=case when &prev_condition or (&condition);

    %end;

    %put new_condition is &new_condition;

    %let rc=%sysfunc(fetch(&table_id));   

    %end;

    %let rc=%sysfunc(close(&table_id));

%end;

%mend;

%rules(have);

What i want in output is 

new_condition is case when ((code1) or (code7))

new_condition is case when ((code2) or (code3))

new_condition is case when ((code2) or (code3))

new_condition is case when ((code4))

new_condition is case when ((code5) or (code6))

However, my last observation and first observation gets lost in loop. Please share your views.. Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Adding both FIRST. and LAST. is a little harder.

Try this.  I also added () around the values of the CONDITION variable.

%macro rules (table_name);

  %local label1 label2 label3 condition ;

  %local prev_label1 prev_label2 prev_label3 ;

  %local table_id rc n first new_condition ;

/* open the table */

%let table_id=%sysfunc(open(&table_name,i));

%if not &table_id %then %do;

  %put Unable to open &table_name ;

  %goto quit;

%end;

/* Link dataset variables to macro variables */

%syscall set(table_id);

%let n=0;

%do %while (not %sysfunc(fetch(&table_id)));

  %let n=%eval(&n + 1);

  %let first=%eval( &n=1

      or %superq(prev_label1) ne %superq(label1)

      or %superq(prev_label2) ne %superq(label2)

      or %superq(prev_label3) ne %superq(label3)

      )

  ;

/* Build up new_condition and emit previous condition */

  %if &first %then %do;

     %if (&n > 1) %then %put new_condition is case when (&new_condition) ;

     %let new_condition=(%trim(&condition));

  %end;

  %else %let new_condition=&new_condition or (%trim(&condition)) ;

/* Save values for next iteration */

    %let prev_label1=%superq(label1);

    %let prev_label2=%superq(label2);

    %let prev_label3=%superq(label3);

%end;

%let rc= %sysfunc(close(&table_id));

%if (&n) %then %put new_condition is case when (&new_condition) ;

%else %put No Conditions;

%quit:

%mend;

%rules(have);

new_condition is case when ((code1) or (code7))

new_condition is case when ((code2) or (code3))

new_condition is case when ((code4))

new_condition is case when ((code5) or (code6))

View solution in original post

22 REPLIES 22
Peter_C
Rhodochrosite | Level 12

Why force it into the macro language?

forumsguy
Fluorite | Level 6

@Peter C.. Thats my requirement. This is prod code and cant be changed.. Hence i am stuck with this.. :smileycry:

Peter_C
Rhodochrosite | Level 12

it would be easier to do the same thing in a data step.

If it is prod code why does it need to change?

My suggestion is

Data null ;

Call execute( '%nrstr( %%p)ut new condition is case when ( ' ) ;

Do until( last.label3) ;

Set your.data;

By label1 label2 label3 ;

Call execute( '( ' !! condition !! ' ) ' ) ;

If not last.label3 then

Call execute( ' or ' );

End ;

Call execute( ' ) ' ) ;

Run;

forumsguy
Fluorite | Level 6

Hi Peter,

I was just curious how to get this code working with a slight modification..

Like

Data _null_ ;

Call execute('case when  label1='||label1||' 'and label2='||label2|| 'and label3'='||label3'and''( ' ) ;

Do until( last.label3) ;

Set have;

By label1 label2 label3 ;

Call execute( '( ' !! CONDITION !! ' ) ' ) ;

If not last.label3 then

Call execute( ' or ' );

End ;

Call execute( ' ) ' ) ;

Run;


But its giving errors.. can you please help me out with solution of this??? thanks in advance and helping me to learn new things

Peter_C
Rhodochrosite | Level 12

Why use LABEL1  before the SET statement?

I would suggest placing that definition of the "fixed part" after the BY statement and only when FIRST.LABEL3

Do you need me to write the syntax?

forumsguy
Fluorite | Level 6

Not the complete code.. just call execute  first part only which contains the and statement.  It would be of great help.. if you guide.. since I am getting different errors every time ...

Peter_C
Rhodochrosite | Level 12

Ok

here is how to investigate the problem(s) with the code you posted earlier.

Perhaps the problems all lay in the line.

 

Call execute('case when  label1='||label1||' 'and label2='||label2|| 'and label3'='||label3'and''( ' ) ;

  

rearrange it with fixed pitch text and white space to align it

      

It could be rearranged as

Call execute(

'case  when label1=' || label1 ||' 

      'and label2=' || label2 ||   

      'and label3'='|| label3 'and' '( '

           ) ;

Now I can see the problem!

The code won’t align properly because the quotes are mis-placed.

.

I expect you understand the difference between constants which appear within quotes, and the names of variables. 

And perhaps you just find it difficult to adapt other peoples’ code. That earlier code of mine was untested and bug-infested 😉

Because there are no comparisons (GE, LT, EQ, etc.) in your code above, the only possible problem caused by adding that “white space” is that you will get more white space on your output. However, it helps to spot where the code has become “unbalanced”.

I consider the layout of code the biggest assistance to de-bugging.

Next time your code gives you a problem, lay it out to match the processing you need – stage by stage. Only once you are satisfied that all the piece you need are in place, will you be ready to collapse white space, functions and statements.

Layout is a big part of how I debug/analyse programs. You may have better ideas.

Once that quoting is properly placed, you’ll find you need to prefix the call execute() output (which goes into the submit stream immediately after this step completes because that is the way call execute() works). I would suggest an asterisk because then the strings generated will appear within a comment.

Much improvement is needed to make the whole thing work. Having gone through a testing exercise, I’m beginning to realise that my (untested) suggestion in the earlier posting was missing a few features, (like:1  generating a closing semicolon, 2:  STOPping at the right point).

Rather than populate a macro variable directly through call execute(), I’m beginning to see a better solution might write the logic code to a file. That could then be invoked by %include.

Mostly the change is just a matter of replacing call execute() with PUT statements.


Here is one I prepared with generated sql code in mind

*'; *"; */; * clean up unbalanced quotes;

Data _null_ ;

file 'generated logic code.txt' ;

if enough then DO ;

  PUT ' END ; ' ;

  STOP  ;

END;

* data step iteration for a row ;

if _n_ = 1 then PUT '%LET LOGIC_CODE= CASE '  ;

  Do until( last.label3) ;

  * do loop iteration for each condition ;

  * i.e. each condition within one set of label1-3 ;

  Set have end= enough ;

    By label1 label2 label3 ;

  * on first for a set of label1-3 report those labels ;

    If first.label3 then  PUT ' WHEN ' label1= '& '

                                    label2= '& '

                                    label3= '& '  '(' ;

  * "named" put style with those "=" is really helpful ;

FORMAT LABEL1-LABEL3 $QUOTE200. ;

  * the FORMAT statement directs the PUT to quote the values of label1-3 ;

  * now for each CONDITION, report it with ( parentheses ) ;

    PUT '( ' CONDITION  ' ) '  ;

  * if not last for a set of label1-3 need to say OR ;

    If not last.label3 then PUT ' OR '  ;

  End ;

  * now after all conditions reported, close with another ) ;

  PUT ' )  '  ;

Run;

* now check the generated text ;

PROC FSLIST FILE=  'generated logic code.txt' ;

RUN;


forumsguy
Fluorite | Level 6

Superb sir.... u were absolutely right in quotes.. and the way you explained is fantastic.  I will keep every thing in mind before I try other people's codes here...

Tom
Super User Tom
Super User

Look into using %SYSCALL SET(...) statement.  This will eliminate the need to pull the variable values into macro variables.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Vince28_Statcan
Quartz | Level 8

Hi Forumsguy,

I am not sure this is what you are looking for based on the output you said you wanted, however, I assumed from the code that your intentions were cases where all 3 labels are identical to be output on a single line and only once. I simply added a tracker of "single codes" and slightly adapted your %put statements.

However, if what I did was not what you were looking for, here is some clues as to why your output is odd. The %put outside at the end of your do while effectively duplicates all %put in the case where labels are equal. If it is your way around to douple parse such cases, you may as well just duplicate the put statement in your do segment rather than use one outside. This will allow you to control the %else statement with a boolean-like variable telling you if you were ongoing a "single label" versus a pair (or more) of labels. In other words, it would be best if you had your %put statements each within the do/else and not one outside.

I am not entirely sure why you mentionned you were "losing" the first and last record of your DS with the example you've mentionned as to what output you really want.

If you wanted an output like

new_condition is case when ((code1) or (code7))

new_condition is case when ((code1) or (code7))

new_condition is case when ((code2) or (code3))

new_condition is case when ((code2) or (code3))

new_condition is case when ((code4))

new_condition is case when ((code5) or (code6))

new_condition is case when ((code5) or (code6))

That is, effectively duplicating all including first and last observations, you can simply copy past the %put statement in the labels are equal %do segment in the code I provided.

Hope this helps.

Vincent

Tom
Super User Tom
Super User

Looks like you are trying to mimic FIRST. processing in macro code?

Try this.

data have;

input label1 $ label2 $ label3 $ condition $ ;

cards;

a d    p code1

a d    p code7

b e    q code2

b e    q code3

c p    . code4

d a    r code5

d a    r code6

;

run;

%macro rules (table_name);

  %local label1 label2 label3 condition ;

  %local prev_label1 prev_label2 prev_label3 prev_condition ;

  %local table_id rc n first new_condition ;

/* open the table */

%let table_id=%sysfunc(open(&table_name,i));

%if not &table_id %then %do;

  %put Unable to open &table_name ;

  %goto quit;

%end;

/* Link dataset variables to matching macro variables */

%syscall set(table_id);


/* Read all observations */

%let n=0;

%do %while (not %sysfunc(fetch(&table_id)));

  %let n=%eval(&n + 1);

  %let first=%eval( &n=1

      or %superq(prev_label1) ne %superq(label1)

      or %superq(prev_label2) ne %superq(label2)

      or %superq(prev_label3) ne %superq(label3)

      )

  ;

/* Build up new_condition */

  %if &first %then %let new_condition=&condition;

  %else %let new_condition=&new_condition or &condition ;

  %put n=&n first=&first new_condition is case when (&new_condition) ;

/* Save values for next iteration */

    %let prev_label1=%superq(label1);

    %let prev_label2=%superq(label2);

    %let prev_label3=%superq(label3);

    %let prev_condition=%superq(condition);

%end;

%let rc= %sysfunc(close(&table_id));

%quit:

%mend;

%rules(have);

n=1 first=1 new_condition is case when (code1)

n=2 first=0 new_condition is case when (code1 or code7)

n=3 first=1 new_condition is case when (code2)

n=4 first=0 new_condition is case when (code2 or code3)

n=5 first=1 new_condition is case when (code4)

n=6 first=1 new_condition is case when (code5)

n=7 first=0 new_condition is case when (code5 or code6)

Tom
Super User Tom
Super User

Adding both FIRST. and LAST. is a little harder.

Try this.  I also added () around the values of the CONDITION variable.

%macro rules (table_name);

  %local label1 label2 label3 condition ;

  %local prev_label1 prev_label2 prev_label3 ;

  %local table_id rc n first new_condition ;

/* open the table */

%let table_id=%sysfunc(open(&table_name,i));

%if not &table_id %then %do;

  %put Unable to open &table_name ;

  %goto quit;

%end;

/* Link dataset variables to macro variables */

%syscall set(table_id);

%let n=0;

%do %while (not %sysfunc(fetch(&table_id)));

  %let n=%eval(&n + 1);

  %let first=%eval( &n=1

      or %superq(prev_label1) ne %superq(label1)

      or %superq(prev_label2) ne %superq(label2)

      or %superq(prev_label3) ne %superq(label3)

      )

  ;

/* Build up new_condition and emit previous condition */

  %if &first %then %do;

     %if (&n > 1) %then %put new_condition is case when (&new_condition) ;

     %let new_condition=(%trim(&condition));

  %end;

  %else %let new_condition=&new_condition or (%trim(&condition)) ;

/* Save values for next iteration */

    %let prev_label1=%superq(label1);

    %let prev_label2=%superq(label2);

    %let prev_label3=%superq(label3);

%end;

%let rc= %sysfunc(close(&table_id));

%if (&n) %then %put new_condition is case when (&new_condition) ;

%else %put No Conditions;

%quit:

%mend;

%rules(have);

new_condition is case when ((code1) or (code7))

new_condition is case when ((code2) or (code3))

new_condition is case when ((code4))

new_condition is case when ((code5) or (code6))

forumsguy
Fluorite | Level 6

@TOM.. Amazing... its working .. However, it gives me one minor error....

The keyword parameter  was not defined with the macro... do you have idea what it could be for ?? I guess its related to SAS installation folder

ballardw
Super User

Errors related to keyword parameter not defined usually point to a call in a macro like

%dummy(parm1=somevalue), but when the macro was defined as in %macro dummy([parameters]) did not include parm1 with the = . Without the = the parameter is positional and not referenced as (parm1=).

If you tried calling Tom's macro with %rules(table_name=) that would be the cause.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 22 replies
  • 3367 views
  • 7 likes
  • 6 in conversation