topic Re: Is there a Do-Loop solution for this? in SAS Programming

Is there a Do-Loop solution for this?

ErinRoberts — Wed, 04 Apr 2018 12:21:37 GMT

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?

Re: Is there a Do-Loop solution for this?

Astounding — Wed, 04 Apr 2018 12:38:45 GMT

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;

Re: Is there a Do-Loop solution for this?

PaigeMiller — Wed, 04 Apr 2018 12:36:05 GMT

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

Re: Is there a Do-Loop solution for this?

RW9 — Wed, 04 Apr 2018 12:44:31 GMT

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.

Re: Is there a Do-Loop solution for this?

ErinRoberts — Wed, 04 Apr 2018 12:58:50 GMT

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.

Re: Is there a Do-Loop solution for this?

ErinRoberts — Wed, 04 Apr 2018 13:04:29 GMT

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.