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

Hi,

 

I have multiple indicator variables and I need to find the first occurence of specific value among them and replace the next values with another value. 

 

Sample of my data looks like 

id year sex a1 a2 a3 a4 a5 
1 1994  1   1  1  2  2  2 
2 1992  1   1  4  4  4  4
3 1992  2   1  2  3  3  4
4 1991  1   1  1  4  4  4

What I'm trying to is to detect if variable a1--a5 has a value 4 before value 2 or 3 have occurenced, and replace the first occurence of value for with 2 and the next values with 3. 

 

I have tried to do that with arrays, but I haven't find out how to detect the first occurence of value and replace it with another value.. 

 

So the data should look like 

 

id year sex a1 a2 a3 a4 a5 
1 1994  1   1  1  2  2  2 
2 1992  1   1  2  3  3  3
3 1992  2   1  2  3  3  4
4 1991  1   1  1  2  3  3

Any help is highly appreciated! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
KachiM
Rhodochrosite | Level 12

Better understood now

data have;
input id year sex a1 a2 a3 a4 a5;
datalines; 
1 1994  1   1  1  2  2  2 
2 1992  1   1  4  4  4  4
3 1992  2   1  2  3  3  3
4 1991  1   1  2  4  4  4
5 1992  2   1  2  2  4  4
6 1985  2   1  3  4  4  4 
7 1988  2   1  3  3  3  3 
;
run;

data need;
   array k[5] _temporary_;
   do until(eof);
      set have end = eof;
      array a a1--a5;
      do i = 1 to dim(k);
         k[i] = a[i];
      end;
      /** Process the conditions **/
     do i = 1 to dim(k);
      if k[i] = 4 then do;
      
         if k[i-1] = 2 then do;
            do j = i to dim(k);
               k[j] = 3;
            end;
         end;
      
         else if k[i-1] = 3 then do;
            do j = i to dim(k);
               k[j] = 2;
            end;
         end;
      
         else if k[i-1] = 1 then do;
            k[i] = 2;
            do j = i+1 to dim(k);
               k[j] = 3;
            end;
         end;
      end;
   end;
   do i = 1 to dim(k);
      a[i] = k[i];
   end;
   output;
end;
drop i j;
run;


Obs	id	year	sex	a1	a2	a3	a4	a5
1	1	1994	1	1	1	2	2	2
2	2	1992	1	1	2	3	3	3
3	3	1992	2	1	2	3	3	3
4	4	1991	1	1	2	3	3	3
5	5	1992	2	1	2	2	3	3
6	6	1985	2	1	3	2	2	2
7	7	1988	2	1	3	3	3	3

.

It is easier to copy a1 - a5 into a _temporary_ array first.

Then process the array for 4, and apply the conditions.

Then copy the elements of array to a1 - a5 and write the output.

 

View solution in original post

7 REPLIES 7
KachiM
Rhodochrosite | Level 12

Here is array way.

Hope this is acceptable.

As you are just new to the Community, please see a wheel on the top right. Click the arrow. Mark my answer as you please.

data have;
input id year sex a1 a2 a3 a4 a5;
datalines; 
1 1994  1   1  1  2  2  2 
2 1992  1   1  4  4  4  4
3 1992  2   1  2  3  3  4
4 1991  1   1  1  4  4  4
;
run;

data want;
   set have;
   array a[5] a1 a2 a3 a4 a5;

   retain flag 0;
   retain a4pos;
   do i = 1 to 5;
      if a[i] = 2 or a[i] = 3 then do;
         flag = 1;
         if i > 3 then leave;
      end;
      if flag = 1 and a[i] = 4 then do; a[i] = 2; a4pos = i + 1; leave; end;
   end;
   if flag = 1 and a4pos ^= . then do i = a4pos to 5;
      a[i] = 3;
   end;
   a4pos = .;
drop i flag a4pos;
run;

Obs	id	year	sex	a1	a2	a3	a4	a5
1	1	1994	1	1	1	2	2	2
2	2	1992	1	1	2	3	3	3
3	3	1992	2	1	2	3	3	4
4	4	1991	1	1	1	2	3	3

 

Laura_123
Calcite | Level 5

Hi! 

 

Thanks for your reply. This works perfectly, but then I noticed that in my data exists couple of subjects (my data consist 20 000 rows) who has obs like this 

7 1994  1   1  3  4  4  4

 So now those rows become 

7 1994  1   1  3  2  3  3

 And what I want them to become is 

7 1994  1   1  3  2  2  2

 

Didin't realize when asking the question that there might be also obs like this.. 

 

(So that there would be some kind of logic why I want create rows like this, is that those numbers indicate subjects marital status in year(1 to 5). So that 1=single 2=married 3=child and this number 4 is married & child. And I want separate that value. If subject has value 4 after being single, then it should be first married and then child (that's how it done right now). But if subject has first child and then has a value number 4 then it should be coded that first child(=3) then married(=2).)

KachiM
Rhodochrosite | Level 12

Better you place an input data set with the revised requirements and show how you want the output to looklike. This will worth 1000 words.

Laura_123
Calcite | Level 5

Yeah, maybe you're right. 

Here's example rows of all kind of combinations that exists in my data 

 

id year sex a1 a2 a3 a4 a5 
1 1994  1   1  1  2  2  2 
2 1992  1   1  2  3  3  3
3 1992  2   1  2  3  3  3
4 1991  1   1  1  2  3  3
5 1990 2 1 2 2 4 4
6 1985 2 1 2 4 4 4
7 1992 2 1 3 4 4 4
8 1993 1 1 4 4 4 4

 So basically the idea is to replace value for depenging what value occurs before that. 

If value before 4 is 2 then number 4 should be replaced with 3. 

If value before 4 is 3 then number 4 should be replaced with 2 

if value before 4 is 1 then number 4 and the next one should be replaced with 2 & 3. 

 

And the desired output:

 

id year sex a1 a2 a3 a4 a5 
1 1994  1   1  1  2  2  2 
2 1992  1   1  2  3  3  3
3 1992  2   1  2  3  3  3
4 1991  1   1  1  2  3  3
5 1990 2 1 2 2 3 3
6 1985 2 1 2 3 3 3
7 1992 2 1 3 2 2 2
8 1993 1 1 2 3 3 3

 

Laura_123
Calcite | Level 5

Yeah, you're right. 

 

So here's all kind of combinations that exists my data 

 

id year sex a1 a2 a3 a4 a5 
1 1994  1   1  1  2  2  2 
2 1992  1   1  4  4  4  4
3 1992  2   1  2  3  3  3
4 1991  1   1  2  4  4  4
5 1992 2 1 2 2 4 4
6 1985 2 1 3 4 4 4
7 1988 2 1 3 3 3 3

 And the data what I want:

id year sex a1 a2 a3 a4 a5 
1 1994  1   1  1  2  2  2 
2 1992  1   1  2  3  3  3
3 1992  2   1  2  3  3  3
4 1991  1   1  2  3  3  3
5 1992 2 1 2 2 3 3
6 1985 2 1 3 2 2 2
7 1988 2 1 3 3 3 3

 So if number 4 exists, then I want to detect which value occurs before that. 

 

If value before 4 is 2 then number 4 should be replaced with 3 

If value before 4 is 3 then number 4 should be replaced with 2

If value before 4 is 1 then number 4 and the next one should be replaced with 2 and 3 

KachiM
Rhodochrosite | Level 12

Better understood now

data have;
input id year sex a1 a2 a3 a4 a5;
datalines; 
1 1994  1   1  1  2  2  2 
2 1992  1   1  4  4  4  4
3 1992  2   1  2  3  3  3
4 1991  1   1  2  4  4  4
5 1992  2   1  2  2  4  4
6 1985  2   1  3  4  4  4 
7 1988  2   1  3  3  3  3 
;
run;

data need;
   array k[5] _temporary_;
   do until(eof);
      set have end = eof;
      array a a1--a5;
      do i = 1 to dim(k);
         k[i] = a[i];
      end;
      /** Process the conditions **/
     do i = 1 to dim(k);
      if k[i] = 4 then do;
      
         if k[i-1] = 2 then do;
            do j = i to dim(k);
               k[j] = 3;
            end;
         end;
      
         else if k[i-1] = 3 then do;
            do j = i to dim(k);
               k[j] = 2;
            end;
         end;
      
         else if k[i-1] = 1 then do;
            k[i] = 2;
            do j = i+1 to dim(k);
               k[j] = 3;
            end;
         end;
      end;
   end;
   do i = 1 to dim(k);
      a[i] = k[i];
   end;
   output;
end;
drop i j;
run;


Obs	id	year	sex	a1	a2	a3	a4	a5
1	1	1994	1	1	1	2	2	2
2	2	1992	1	1	2	3	3	3
3	3	1992	2	1	2	3	3	3
4	4	1991	1	1	2	3	3	3
5	5	1992	2	1	2	2	3	3
6	6	1985	2	1	3	2	2	2
7	7	1988	2	1	3	3	3	3

.

It is easier to copy a1 - a5 into a _temporary_ array first.

Then process the array for 4, and apply the conditions.

Then copy the elements of array to a1 - a5 and write the output.

 

Laura_123
Calcite | Level 5

Thank you! That solved my problem. 🙂 

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
  • 7 replies
  • 2739 views
  • 0 likes
  • 2 in conversation