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

I cannot write my own %do %until code to do what I wanted to do.

I have a dataset (please see the first table. I want to convert the first table into the second table. Any missing status in between two "1"s should be marked as "1".

Any help? Thanks!!

-----

IDStatus 1Status 2Status 3Status 4Status 5Status 6
11..1..
2.1.1..
31....1
4.1....

o, neeStatus 1



IDStatus 1Status 2Status 3Status 4Status 5Status 6
11111..
2.111..
3111.1.1.1
4.1....

Status 1

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

Oh well.

data status;
   array status[7];
   array r
  • status7-status1;
  •    input status
  • ;
  •    f = whichn(1,of status
  • );
  •    if f then do;
          l =
    1+dim(status)-whichn(1,of r
  • );
  •       do _n_ = f+1 to l-1;
             status[_n_]=
    1;
            
    end;
         
    end;
      
    datalines;
    1 . 1 . 1 . .
    . . 1 . . . 1
    . 1 . 1 . . .
    . . 1 . . . .
    . . . . . . .
    ;;;;
    run;

    View solution in original post

    8 REPLIES 8
    Reeza
    Super User

    you don't need %do or %until, you need a do loop in a data step.

    Pseudocode:

    1. Create a string that appends all characters together - use a variation of cat function

    2. Find the start, the first 1 - use index function.

    3. Find the end, the second1 if it exists, use index function again, reversing the string and searching from the end. subtract from length to find the number.

    4. Loop from start to end and replace with 1's

    Ken_oy
    Fluorite | Level 6

    Thanks Reeza. Your answer is really helpful!

    But in some of my datasets, I have more than 300 status. Creating a new variable might not be long enough to cover all status.

    I am still thinking of using a do loop to read the first "1" and the last "1", and then go and follow your instruction 4.

    Reeza
    Super User

    A 400 character length string shouldn't be an issue, just make sure to declare the length ahead of time.

    data_null__
    Jade | Level 19

    Is there ever more than two status variables that are 1?

    You show

    1 . . 1 .  .

    but can you have

    1 . . 1 . . . 1

    and if so what how you fill?

    data_null__
    Jade | Level 19

    Oh well.

    data status;
       array status[7];
       array r
  • status7-status1;
  •    input status
  • ;
  •    f = whichn(1,of status
  • );
  •    if f then do;
          l =
    1+dim(status)-whichn(1,of r
  • );
  •       do _n_ = f+1 to l-1;
             status[_n_]=
    1;
            
    end;
         
    end;
      
    datalines;
    1 . 1 . 1 . .
    . . 1 . . . 1
    . 1 . 1 . . .
    . . 1 . . . .
    . . . . . . .
    ;;;;
    run;
    Praveen_SAS
    Calcite | Level 5

    @ken_oy:    

           Hi can you have try on this.

    data gh;

      input a b c h l k p;

    datalines;

    1 . 1 . 1 . 1

    . . 1 . . . 1

    . 1 . 1 1 . .

    ;run;

    data fg(drop = x mark i j);

      set gh;

      array f

  • _numeric_;
  •   x=0;

      do i = 1 to dim(f);

      if f ^= . then x+1;

      end;

      if x > 1 then do;

      mark=0;

      do j = 1 to dim(f);

      if f ^= . then mark+1;

      if mark not in (. 0) and x - mark >= 1 and f = . then f  = 1;

      end;

      end;

    run;

    Astounding
    PROC Star

    I don't see how macro language would be useful here, but try this:

    data want;

       set have;

       array status {300} status_1 - status_300;

       do _n_=1 to 300 until first_1 > .;

            if status{_n_}=1 then first_1=_n_;

       end;

       do _n_=300 to 1 by -1 until last_1 > .;

          if status{_n_}=1 then last_1=_n_;

       end;

       if first_1 < last_1 then do _n_=first_1 to last_1-1;

          status{_n_}=1;

       end;

       drop first_1 last_1;

    run;

    asasuser
    Calcite | Level 5

    data status;

       array status[6];

       array r

  • status1-status6;
  •    input status

  • ;
  •    do i = 1 to dim(r) while(r(i) = .) ;

       End ;

       do j = dim(r) to i by - 1 while(r(j) = .) ;

       End ;

       Do k = i to j ;

       r(k) = 1 ;

       End ;

       drop i j k ;

       datalines;

    1 . . 1 . .

    . 1 . 1 . .

    1 . . . . 1

    . 1 . . . .

    ;;;;

    run;

    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!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    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
    • 8 replies
    • 1459 views
    • 6 likes
    • 6 in conversation