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

Hi all,

I am using SAS EG and a macro with 'if then else' and  'do loop' to create a 'new variable' with values of '1' or '0' based on the values of more than 1 variables (pa, pa1 - pa4, lt and lp). Below is the structure of my dataset called 'have'

 

id pa pa1 pa2 pa3 pa4 lt lp
001 5 5 5 5   1 9
002 6 6 6     1 3
003 7 7 7 7 7 2 2

 

 bellow is the dataset I need called 'want'

id pa pa1 pa2 pa3 pa4 lt lp new_variable
001 5 5 5 5   1 9 1
002 6 6 6     1 3 0
003 7 7 7 7 7 2 2 1

 

 %macro midds;

 data want;

set have;

if (pa in (&og.) %do i=1 %to 4; or pa&i. in (&og.) %end; and

(lt='1' and (lp in (&tw.))then new_variable='1';

else new_variable='0';

run;

%mend;

%midds;

 

&og is a macro for all required values in pa, pa1- pa4

&tw is a macro for all required values in lp

 

When I run the code, I get an error which says 'no matching if then clause'. Could you please point out to me what is wrong with my code? Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Your value for the last sample row does not look right. LT is not '1' in that case.

Not sure why you are using '1' and '0' instead of 1 and 0.   I have made the new variable as numeric as it is easier.  But if you convert it to character then remember to change the last assignment into a IF/THEN/ELSE block instead of just the evaluation of a boolean expression.

data have ;
input id pa pa1 pa2 pa3 pa4 lt $ lp expected ;
cards;
001 5 5 5 5 . 1 9 1
002 6 6 6 . . 1 3 0
003 7 7 7 7 7 2 2 1
;

%let og=5 7;
%let tw=9 2;

data want ;
 set have ;
 array X pa pa1-pa4 ;
 new_var=0;
 do i=1 to dim(x) while (new_var=0);
   if x(i) in (&og) then new_var=1 ;
 end;
 new_var = new_var and lt='1' and lp in (&tw) ;
 drop i;
run;
proc print;
run;

Results

Obs    id    pa    pa1    pa2    pa3    pa4    lt    lp    expected    new_var

 1      1     5     5      5      5      .     1      9        1          1
 2      2     6     6      6      .      .     1      3        0          0
 3      3     7     7      7      7      7     2      2        1          0

 

View solution in original post

9 REPLIES 9
novinosrin
Tourmaline | Level 20

I do not comprehend why you need a macro. Can you please let us know the condition to populate the new _var in plan sentences as opposed to your macro illustration. I believe we can help you with a simple solution. Thank you

dr2014
Quartz | Level 8

@novinosrinThe macro was available and I wanted to make use of it.

 

This is what I am trying to accomplish. Here are the basic statements...

 

%let og=(5,6,7);

%let tw=(9,3);

 

data want;

  set have;

if ( a in (&og.) or

  pa1 in (&og.) or

  pa2 in (&og.) or

  pa3 in (&og.) or

  pa4 in (&og.) ) and

  ( lt=1 and lp in (&tw.) ) then new_variable =1;

                                       else new_variable=0; 

run;

 

 I wanted to avoid using the multiple 'or' syntax hence the macro.

 

Please let me know if there is way to avoid that and/or make use of the macro. Also I had an error in the dataset 'want' and I have copy pasted both below. The 'have' dataset have is large and there are multiple records for each id.

 

have

id
pa pa1 pa2 pa3 pa4 lt lp
1 5 5 5 5   1 9
2 6 6 6     1 3
3 7 7 7 7 7 2 2

 

want

id

pa pa1 pa2 pa3 pa4 lt lp new_variable
1 5 5 5 5   1 9 1
2 6 6 6     1 3 1
3 7 7 7 7 7 2 2 0
Tom
Super User Tom
Super User

It would be much easier to just generate a simple "function-style" macro.

%macro or_list(names,values);
%local i sep;
%do i=1 %to %sysfunc(countw(&names));
&sep %scan(&names,&i) in (&values)
%let sep=or;
%end;
%mend or_list;

Then use that in your data step.

new_variable = (%or_list(pa pa1 pa2 pa3 pa4,&og.)) and lt=1 and lp in (&tw.) ;
Astounding
PROC Star

The prior statement is missing two closed parentheses.  I would guess (but can't really know) that they belong here:

 

if (pa in (&og.) %do i=1 %to 4; or pa&i. in (&og.) %end; ) and

(lt='1' and (lp in (&tw.))then new_variable='1';

Patrick
Opal | Level 21

@dr2014

Always try and make your code work without macro coding for a sample case before you dive into generalizing. In your case array processing could eventually get the job done as well.

 

Below code should bring you closer to where you want to be.

data have;
  infile datalines dsd truncover;
  input id pa pa1 pa2 pa3 pa4 lt lp;
datalines;
1,5,5,5,5,,1,9
2,6,6,6,,,1,3
3,7,1,7,7,7,2,2
;
run;

%let og=pa pa1 pa2 pa3 pa4;
%let tw=9,3,2;
data want(drop=_i);
  set have;
  hit=0;

  array a_pa {*} &og;
  call sortn(of a_pa[*]);
  do _i=1 to dim(a_pa)-1;
    if a_pa[_i]=a_pa[_i+1] then
      do;
        if lt='1' and lp in (&tw)then
          do;
            hit=1;
            leave;
          end;
      end;
  end;

  /* "restore" PDV so PA variable/value combinations are as per source */
  set have (keep=&og);
run;

On a side note:

Given that in your logic macro variable &og will contain the list of PA<n> variables the code bit pa&i. in (&og.) will always be true as pa&i will always also be a variable in your &og list (=comparing a variable with itself).

dr2014
Quartz | Level 8
Thanks for your reply @Patrick, however &og is not pa pa1 pa2 pa3 pa4 but the values in it which are many in the real dataset. Please review my reply to @novinosrin
Tom
Super User Tom
Super User

Your value for the last sample row does not look right. LT is not '1' in that case.

Not sure why you are using '1' and '0' instead of 1 and 0.   I have made the new variable as numeric as it is easier.  But if you convert it to character then remember to change the last assignment into a IF/THEN/ELSE block instead of just the evaluation of a boolean expression.

data have ;
input id pa pa1 pa2 pa3 pa4 lt $ lp expected ;
cards;
001 5 5 5 5 . 1 9 1
002 6 6 6 . . 1 3 0
003 7 7 7 7 7 2 2 1
;

%let og=5 7;
%let tw=9 2;

data want ;
 set have ;
 array X pa pa1-pa4 ;
 new_var=0;
 do i=1 to dim(x) while (new_var=0);
   if x(i) in (&og) then new_var=1 ;
 end;
 new_var = new_var and lt='1' and lp in (&tw) ;
 drop i;
run;
proc print;
run;

Results

Obs    id    pa    pa1    pa2    pa3    pa4    lt    lp    expected    new_var

 1      1     5     5      5      5      .     1      9        1          1
 2      2     6     6      6      .      .     1      3        0          0
 3      3     7     7      7      7      7     2      2        1          0

 

dr2014
Quartz | Level 8

Thanks for your reply @Tom Yes, there was an error in my value for the 'new_variable' in the last sample and also it doesn't make a difference if the values for new_variable is character or numeric. Let me try your code. An array could be a better solution than the macro. I will get back to the post in a bit.

dr2014
Quartz | Level 8

Thanks for your help it worked!

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1484 views
  • 2 likes
  • 5 in conversation