BookmarkSubscribeRSS Feed
Saravanan
Fluorite | Level 6


Hi,

I have a table.

Measure1Measure2Measure3 Measure4Measure5Measure6Measure7Measure8Measure9Total ScoreTarget Score
1234567894560
345671012152587100
24684547586061291350
14681012152527108130
467891013152092130
27891013151620100100
1234567894560
67891011151620102130
579111516171820118130

total score is sum of measure 1 through measure 9.

I have to increase measure1 by 1 till it reaches the value of measure2, if the total score reaches the target score before reaching the value of measure2 then it should exit the loop.

if total score not equal to target score after measure1 equals the value of measure2, then measure1 should increase first and then measure2, vice versa till it equals the value of measure3. if the total score reaches the target score before reaching the value of measure3 then it should exit the loop.

if total score not equal to target score after measure1 & measure2 equals the value of measure3,  then measure1 should increase first, then measure2 and then measure3, vice versa till it equals the value of measure4.  if the total score reaches the target score before reaching the value of measure4 then it should exit the loop. the cycle continues till measure9.

Help me achieve this goal.  Your help is greatly appreciated.

Thanks,

Saravanan

9 REPLIES 9
Haikuo
Onyx | Level 15

Hi,

Not sure if this is what you need, and not sure what do you want to do if maxing out the measure9 while still not reaching the target?

data have;

input Measure1    Measure2    Measure3    Measure4    Measure5    Measure6    Measure7    Measure8    Measure9    Total_Score    Target_Score;

cards;

1    2    3    4    5    6    7    8    9    45    60

3    4    5    6    7    10    12    15    25    87    100

2    4    6    8    45    47    58    60    61    291    350

1    4    6    8    10    12    15    25    27    108    130

4    6    7    8    9    10    13    15    20    92    130

2    7    8    9    10    13    15    16    20    100    100

1    2    3    4    5    6    7    8    9    45    60

6    7    8    9    10    11    15    16    20    102    130

5    7    9    11    15    16    17    18    20    118    130

;

data want (drop=d: i);

set have;

dt=    Target_Score-Total_Score ;

array mea measure:;

array diff(8);

do i=1 to dim(diff);

  diff(i)=mea(i+1)-mea(i);

end;

do while (dt>0);

do i=1 to dim(diff) while (dt>0);

           dt=dt-diff(i);

           mea(i)=mea(i)+diff(i);

           if dt<0 then mea(i)=mea(i)+dt;

       end;   

       do i=1 to dim(diff);

        diff(i)=mea(i+1)-mea(i);

        end;

end;

run;

proc print;run;

Haikuo

updated.

Saravanan
Fluorite | Level 6

Hi Hai,

Thanks for your reply. But i`m not looking for this.

I`m looking for;

first measure1 should increase by 1 until it equals measure2. If total score reaches target score before measure1 equals measure2 then it should exit loop.

if total score is not equal to target score even after measure1 equals measure2 then, measure1 and measure2, both should increase one after another until both measure1 and measure2 equals measure3. if total score equals target score before either measure1 or measure2 reaches measure3 then exit loop. if total score is not equal to target score even after both measure1 and measure2 equals measure3, then measure1, measure2, measure3, all these three should increase one after another until it equals measure4. if total score equals target score before either of measure1 or measure2 or measure3 reaches measure4 then exit loop. like wise it should continue till measure9 if total score not equals target score.

Hope i said it clearly this time.

Thanks,

Saravanan

mkeintz
PROC Star

Let's say that measure 1 has been raised to measure 2, but total_score is still 3 short of target-score. You could raise both measure1 and measure 2 by 1 and still have total_score one short.  Do you then raise measure 2 or measure 1 to get that last point?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Saravanan
Fluorite | Level 6

Thank you for your reply.

let me show it with what should happen in a record.

for example, the record reads as below,

Measure1    Measure2    Measure3    Measure4    Measure5    Measure6    Measure7    Measure8    Measure9    Total_Score    Target_Score

1    2    3    4    5    6    7    8    9    45    55


This is what should happen in step by step process,

1     2     3     4     5     6     7     8     9     45     55

2     2     3     4     5     6     7     8     9     46     55

3     2     3     4     5     6     7     8     9     47     55

3     3     3     4     5     6     7     8     9     48     55

4     3     3     4     5     6     7     8     9     49     55

4     4     3     4     5     6     7     8     9     50     55

4     4     4     4     5     6     7     8     9     51     55

5     4     4     4     5     6     7     8     9     52     55

5     5     4     4     5     6     7     8     9     53     55

5     5     5     4     5     6     7     8     9     54     55

5     5     5     5     5     6     7     8     9     55     55


now the total score(55) equals target score(55) so it should exit loop.

Above is the simulation of one record. Hope this helps.


Thanks,

Saravanan



Haikuo
Onyx | Level 15

Ok, try this one:

data have;

input Measure1    Measure2    Measure3    Measure4    Measure5    Measure6    Measure7    Measure8    Measure9    Total_Score    Target_Score;

cards;

1    2    3    4    5    6    7    8    9    45    60

3    4    5    6    7    10    12    15    25    87    100

2    4    6    8    45    47    58    60    61    291    350

1    4    6    8    10    12    15    25    27    108    130

4    6    7    8    9    10    13    15    20    92    130

2    7    8    9    10    13    15    16    20    100    100

1    2    3    4    5    6    7    8    9    45    60

6    7    8    9    10    11    15    16    20    102    130

5    7    9    11    15    16    17    18    20    118    130

;

data want;

set have;

array mea measure:;

do while (sum(of mea(*))<target_score);

   do i=1 to dim(mea)-1;

         if mea(i)<mea(i+1) then do;

            mea(i)+1;

            i=0;

            if sum(of mea(*))>=target_score then leave;

            end;

         else continue;

      

    end;

end;

drop i;

run;

proc print;run;

Haikuo

updated with 'run;'

Saravanan
Fluorite | Level 6

Looks like this what exactly i want. Thank you very much.

Anyway i will test it tomorrow in real data and let you know.

Thanks a lot.

Saravanan

Saravanan
Fluorite | Level 6

Hi, thank you for the reply.On the same logic, i have two more variations. I think you will be able to crack it in a minute.

These are the variations.

First variation is:

data have;

input Measure1    Measure2    Measure3    Measure4    Measure5    Measure6    Measure7    Measure8    Measure9    Total_Score    Target_Score Indicator1 Indicator2 Indicator3 Indicator4 Indicator5 Indicator6 Indicator7 Indicator9;

cards;

12     15     20     25     30     35     37     40     45     259     350     .     .     1     .     .     .     .     .     .

6     9     12     25     30     32     35     37     40     226     350     .     .     .     .     1     .     .     .     .

12     15     18     20     25     26     28     30     32     206     300     .     .     .     1     .     .     .     .     .    

;

run;

for each of the measure there is indicator associated with it. for colum Measure the corresponding indicator is column Indicator1, for Measure2 the corresponding indicator is Indicator2.

Now i have the same logic as above but with slight variation. If a particular measure has indicator attached to it, it should increment by 10 and the rest of the process is as above with other measures increasing by 1. in above data. for first record, Measure3 has Indicator3 as 1, so it shud increase by 10. In second record, measure5 has Indicator5 as 1, so it should increase by 10 with other measures increasing by 1.

Variation 2:

data have;

input Measure1    Measure2    Measure3    Measure4    Measure5    Measure6    Measure7    Measure8    Measure9    Total_Score    Target_Score Indicator1 Indicator2 Indicator3 Indicator4 Indicator5 Indicator6 Indicator7 Indicator9 impute1 impute2 impute3 impute4 impute5 impute6 impute7 impute8 impute9;

cards;

12     15     20     25     30     35     37     40     45     259     350     .     .     1     .     .     .     .     .     .     .     1     .     .     .     .     .     .     .

6     9     12     25     30     32     35     37     40     226     350     .     .     .     .     1     .     .     .     .     .     .     .     .     1     .     .     .     .

12     15     18     20     25     26     28     30     32     206     300     .     .     .     1     .     .     .     .     .     .     .     .     .     1     .     .     .     .  

;

run;

This is addition to above requirement. This has colums Impute1-Impute9 with it. Each of the Impute1-Impute9 is associated with Measure1-Measure9. If impute column is 1, then the particular measure should not increase. for example, in above record. Mesure3 has Indicator3 as 1, so it should increase by 10, Measure2 has Impute2 as 1, so it should not increase, but other measures should increase as before logic by 1.

for second record, Measure5 has Indicator5 as 1 and Impute5 as 1, so impute5 takes precedence and it should not increase.

The concept is whereever there is Indicator column has 1 the corresponding measure should increase by 10.

Whereever there is Impute column has 1 the corresponding measure should not increase.

Whereever the Impute Indicator column and Impute column is 1, the corresponding measure should not change. All other measure increases by 1 as above logic.

Hope you can help me out.

Thanks,

Saravanan

Haikuo
Onyx | Level 15

Hi Saravanan,

1. The following code tackles your second variation, which is much twisted than the first one. You can workout your first variation easily based on this code, do it as a practice.

2. Since some of your 'measure's increase by '10', there is a chance that your target_score is exceeded, so you need some rules here.

3. If by following your logic and your target_score can NOT be reached, it is not easy to articulate rules from your data to end the loop, so artificial counter has been involved to end it (less than 1000 loops in this case).

data have;

input Measure1    Measure2    Measure3    Measure4    Measure5    Measure6    Measure7    Measure8    Measure9    Total_Score    Target_Score Indicator1 Indicator2 Indicator3 Indicator4 Indicator5 Indicator6 Indicator7 Indicator8 Indicator9 impute1 impute2 impute3 impute4 impute5 impute6 impute7 impute8 impute9;

cards;

12     15     20     25     30     35     37     40     45     259     350     .     .     1     .     .     .     .     .     .  .  .  .     1     .     .        .     .     .     .

6     9     12     25     30     32     35     37     40     226     350     .     .     .     .     1     .    .     .   .  .     .     .     .     1     .     .     .     .   .

12     15     18     20     25     26     28     30     32     206     300     .     .     .     1     .     .     .     .     .  .   .     .     .     .     1     .     .     .     .

;

run;

data want;

set have ;

array mea measure:;

array ind indicator:;

array imp impute:;

array _tmea(1:9) _temporary_;

array _timp(1:9) _temporary_;

array _tind(1:9) _temporary_;

call missing(j,k,ct);

do i=1 to dim(imp);

   if imp(i)=1 then do;

         j+1;

         _timp(j)=mea(i);

    end;

    else do;

          k+1;

          _tmea(k)=mea(i);

          _tind(k)=ind(i);

    end;

end;

do while (sum(of _tmea(*),of _timp(*))<target_score);

  ct+1; if ct>1000 then leave;

   do i=1 to k-1;

              if _tmea(i)<_tmea(i+1) then do;

               if _tind(i) ne 1 then do;

                  _tmea(i)+1;

                  i=0;

                end;

                else do;

                  _tmea(i)+10;

                  i=0;

                

                end;

                if sum(of _tmea(*),of _timp(*))>=target_score then leave;

            end;

            else continue;

   end;

end;

call missing(j,k);

do i=1 to dim(imp);

   if imp(i)=1 then do;

         j+1;

         mea(i)=_timp(j);

    end;

    else do;

          k+1;

          mea(i)=_tmea(k);

    end;

end;

drop i j k ct;

run;

proc print;run;

Haikuo

Saravanan
Fluorite | Level 6

Thank you very much for your prompt reply Hai. I greatly appreciate that.

I am still trying to break your coding logic. Will get back to you if i have any questions and if that is fine with you.

Thanks again.

Saravanan

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
  • 9 replies
  • 776 views
  • 0 likes
  • 3 in conversation