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

I have some code I think could be made simpler using a do-loop but I'm not sure quite how to make it work.

 

DATA WORK.TRANS2;
SET WORK.TRANS_co;
IF PROV_SSN NE SSN1 THEN DEL='Y';
ELSE IF SSN2 NE '' AND PROV_SSN NE SSN2 THEN DEL='Y';
ELSE IF SSN3 NE '' AND PROV_SSN NE SSN3 THEN DEL='Y';

.

.

.

ELSE IF SSN52 NE '' AND PROV_SSN NE SSN52 THEN DEL='Y';

RUN;

 

Basically, I have 52 character variables I'm looking for differences between and if there are any differences, I want to assign DEL='Y'.

 

Any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Actually, scrub that you can use whichc() function:

data work.trans2;
  set work.trans2;
  if whichc(prov_ssn,of ssn:) then del="Y";
run;

Note!! Not tested !!  Provide test data for tested code.

 

What you need is an array;

data work.trans2;
  set work.trans2;
  array ssn{52};
  do over ssn;
    if ssn ne "" and ssn ne prov_ssn then del="Y";
  end;
run;

Note I use the code window (its the {i} above post) and not code all in uppercase.

 

Also, the over is a bit outdated now so to write with an indexer:

data work.trans2;
  set work.trans2;
  array ssn{52};
  do i=1 to 52;
    if ssn{i} ne "" and ssn{i} ne prov_ssn then del="Y";
  end;
run;

Note that I assume (per your text) there are 52 ssnX variables all with a numeric suffix going 1, 2, 3 etc. 

View solution in original post

5 REPLIES 5
Astounding
PROC Star

First step isn't to simplify.  First step is to produce working code.

 

Right now, DEL will always be "Y".  If the first statement doesn't set DEL to "Y", the second one will (ignoring missing values).  After all, your variable won't equal SSN1 and SSN2 at the same time.  So when do you want DEL to be "Y"?

 

It's possible that this matches your intention:

 

del='Y';

array ssn {52};

do k=1 to 52 until (del='N');

   if prov_ssn=ssn{k} then del='N';

end;

ErinRoberts
Calcite | Level 5

Right now the code as I have it written works fine, it's just not user friendly if I have to add or remove variables.

PaigeMiller
Diamond | Level 26

This could be done via a macro %DO loop, but then to execute the entire data step, you would have to embed the data step inside a macro (because you can't have %DO in open code). So, something like this:

 

UNTESTED CODE

 

I repeat

 

UNTESTED CODE

 

%macro dothis;
    data work.trans2;
         set work.trans_co;
         %do i=1 %to 52;
             %if &i>1 %then else if ssn&i ne ' ' and prov_ssn ne ssn&i then del='Y'%str(;);
             %else if prov_ssn ne snn&i then del='Y'%str(;);
         %end;
     run;
%mend;
%dothis
--
Paige Miller
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Actually, scrub that you can use whichc() function:

data work.trans2;
  set work.trans2;
  if whichc(prov_ssn,of ssn:) then del="Y";
run;

Note!! Not tested !!  Provide test data for tested code.

 

What you need is an array;

data work.trans2;
  set work.trans2;
  array ssn{52};
  do over ssn;
    if ssn ne "" and ssn ne prov_ssn then del="Y";
  end;
run;

Note I use the code window (its the {i} above post) and not code all in uppercase.

 

Also, the over is a bit outdated now so to write with an indexer:

data work.trans2;
  set work.trans2;
  array ssn{52};
  do i=1 to 52;
    if ssn{i} ne "" and ssn{i} ne prov_ssn then del="Y";
  end;
run;

Note that I assume (per your text) there are 52 ssnX variables all with a numeric suffix going 1, 2, 3 etc. 

ErinRoberts
Calcite | Level 5
data work.trans2;
  set work.trans_co;
  array ssn{52};
  do i=1 to 52;
    if ssn{i} ne "" and ssn{i} ne prov_ssn then del="Y";
  end;
run;

Does exactly what I need it to thank you!! I've only done this with basic calculations so this was very helpful. 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 868 views
  • 0 likes
  • 4 in conversation