BookmarkSubscribeRSS Feed
GalacticAbacus
Obsidian | Level 7

Hello!

I'm trying to work out how to approach the conditional statements situation I am not familiar with. This could be an example where I need to use conditional loops but I would enjoy some help ^_^ Here is an example to help define my question:

Let's say I'm working with a list of 50 people. These people have signed up to win a cruise via accrual of enough points. Point totals are finalized by month. For whatever reason, points can be negative or positive at the end of a month. The key stipulation is, that if a month total is negative, the resulting negative is a result of the previous month. For example, looking below, March has 49 points. However because April ended up with -35, March actually has 49-35 = 14 points. If this is the case, I am trying to figure out a series of conditional statements to basically make March = 14 points and zero out April. However, multiple months can be negative, as in example 2. Each month value which is negative must be decremented from the most recent positive month. In this case, March =  500 - 50 - 35 = 415. The third condition, is if multiple months are negative, and the most recent positive month - SUM(negative months) < 0 Then the left over negative points must be decremented from the next most recent positive month and so on. Example 3 tries to illustrate this. I'm sure this concept has been encountered before, but I am new at tackling something less than straight forward when dealing with conditional statements. Any help (education on general terms of what I'm trying to do) as well as helpful process or function would be appreciated!

Example 1: A single negative month

Person 1

Month_Points

Jan_00

Feb_55

Mar_49

Apr_-35

May_70

Jun_100

Example 2: Multiple negative months

Month_Points

Jan_00

Feb_55

Mar_500

Apr_-35

May_-50

Jun_100

Example 3: Multiple negative months

Month_Points

Jan_00

Feb_55

Mar_50

Apr_-35

May_-50

Jun_100

Sincerely,

TS

5 REPLIES 5
LinusH
Tourmaline | Level 20

Seems that you have some kind of additive values as basis - actual month values. Stick with that.

Question is what you wish to use your new data set for, and how.

So negative vales should affect next month.What about positive figures, can't read that out from your example.

For clarity, please attach desired result for each scenario.

You result set looks like just report for the current standings, or?

Perhaps just summarize your source table as a report.

Data never sleeps
Ksharp
Super User

So where is your output ? What kind of output would you like ?

data have;

input id Month : monyy7. Points;

format month monyy7.;

cards;

1 Jan2012 0

1 Feb2012 55

1 Mar2012 50

1 Apr2012 -35

1 May2012 -50

1 Jun2012 100

2 Jan2012 0

2 Feb2012 55

2 Mar2012 500

2 Apr2012 -35

2 May2012 -50

2 Jun2012 100

;

run;

proc sort data=have;by id descending month;run;

data want;

set have;

by id;

retain start;

if first.id then call missing(start,sum);

if points lt 0 then start=1;

if start then sum+points;

   else temp=points;

want=coalesce(temp,sum);

if start and sum ge 0 then call missing(start);

drop start temp sum;

run;

Xia Keshan

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, am having to make some assumptions here.  Does the months go over a year?  Are they always sequential.  What happens if all values are - numbers etc. 

Step 1 would be to separate out your data into usable variables, e.g. put month in one, then points in another.  You can then loop over them, or you could create an array, or you could merge data together, or you could use hash lookup.  If its just the 12 months, maybe something like:

data have;

  length points $20;

  person=1;

  points="Jan_00"; output;

  points="Feb_55"; output;

  points="Mar_49"; output;

  points="Apr_-35"; output;

  points="May_70"; output;

  points="Jun_100"; output;

  person=2;

  points="Jan_00"; output;

  points="Feb_55"; output;

  points="Mar_50"; output;

  points="Apr_-35"; output;

  points="May_-50"; output;

  points="Jun_100"; output;

run;

data inter (drop=points);

  set have;

  array months{12} 8.;

  retain months1-months12;

  by person;

  month=month(input("01"||scan(points,1,"_")||"2015",date9.));

  months{month}=input(scan(points,2,"_"),best.);

  if last.person then output;

run;

data want (drop=i);

  set inter;

  array months{12};

  do i=2 to month;

    if months{i} < 0 then do;

      do j=i-1 to 1 by -1;

        if months{j}>0 then do;

          months{j}=months{j}+months{i};

          months{i}=0;

          j=1;

        end;

      end;

    end;

  end;

run;

GalacticAbacus
Obsidian | Level 7

Hi everyone, thank you for the replies! After reading your requests I will add further clarification to facilitate an answer.

Clarification 1: Full date range spans 12 months starting in February. Point accrual could take place between February 1 and January 31 the following year.

Clarification 2: The output above was an example re-arranging of my array into a list form to demonstrate the arithmetic. My actual data set uses 13 variables, (the person, and their point accrual for each month). Variables are actually in the form of Points_Mar, or Points_Feb etc.

Clarification 3: Values can either be a null (.), a negative, or positive integer.

Clarification 4: Output would basically be a re-arranging of point values per month to reflect the actual point totals. I would still have point totals by month, per person, but the monthly totals will have shifted by taking into account negative month totals.

                      (Essentially, month totals which are negative will be aggregated toward the direction of the first month, decremented from positive months, and once subtracted from positive month totals, zeroed.

The situation is basically month totals are determined my month totals upstream. Below is an example of three individuals, hypothetical sample data (Original) and what I'd like to accomplish via code (Modified). I have included a sample of my code so far as a first stab at conditional logic.

Capture.JPG

/*January*/

IF Points_Jan      < 0 THEN January = 0;

ELSE IF Points_Jan >=0 THEN January = Points_Jan;

/*December*/

IF January >= 0     AND Points_Dec >=0 THEN December = Points_Dec;

ELSE IF January >=0 AND Points_Dec < 0 THEN December = 0;

ELSE IF January < 0 AND Points_Dec >=0 THEN December = SUM(Points_Dec,Points_Jan) AND January = 0;

ELSE IF January < 0 AND Points_Dec < 0 THEN December = SUM(Points_Dec,Points_Jan) AND January = 0;

/*November*/

IF December >= 0    AND Points_Nov >=0 THEN November = Points_Nov;

ELSE IF December >=0 AND Points_Nov < 0 THEN November = 0;

ELSE IF December < 0 AND Points_Nov >=0 THEN November = SUM(Points_Nov,Points_Dec) AND December = 0;

ELSE IF December < 0 AND Points_Nov < 0 THEN November = SUM(Points_Nov,Points_Dec) AND December = 0;

TS

GalacticAbacus
Obsidian | Level 7

I believe this is the way I need to do it. Had to create a mid-man variable between initial and adjusted variables. However It's saying my alphabetical variables A - L are uninitialized. I would assume they would be available for use as I'm defining them before they are used down stream in subsequent calculations....weird.

DATA Adjusted_Points;

Set Actual_Points;

/*

-=Existing Point Variables=-

Points_Feb

Points_Mar

Points_Apr

Points_May

Points_Jun

Points_Jul

Points_Aug

Points_Sep

Points_Oct

Points_Nov

Points_Dec

Points_Jan;

*/

/* January  */

IF Points_Jan <0 THEN Jan2 = 0 AND B = SUM(Points_Jan,Points_Dec);

ELSE IF Points_Jan >= 0 THEN Jan2 = Points_Jan AND B = Points_Dec;


/* December  */

IF B <0 THEN Dec2 = 0 AND C = SUM(B,Points_Nov);

ELSE IF B >= 0 THEN Dec2 = Points_Dec AND C = Points_Nov;

/* November  */

IF C <0 THEN Nov2 = 0 AND D = SUM(C,Points_Oct);

ELSE IF C >= 0 THEN Nov2 = Points_Nov AND D = Points_Oct;

/* October  */

IF D <0 THEN Oct2 = 0 AND E = SUM(D,Points_Sep);

ELSE IF D >= 0 THEN Oct2 = Points_Oct AND E = Points_Sep;

/* September  */

IF E <0 THEN Sep2 = 0 AND F = SUM(E,Points_Aug);

ELSE IF E >= 0 THEN Sep2 = Points_Sep AND F = Points_Aug;

/* August  */

IF F <0 THEN Aug2 = 0 AND G = SUM(F,Points_Jul);

ELSE IF F >= 0 THEN Aug2 = Points_Aug AND G = Points_Jul;

/* July  */

IF G <0 THEN Jul2 = 0 AND H = SUM(G,Points_Jun);

ELSE IF G >= 0 THEN Jul2 = Points_Jul AND H = Points_Jun;

/* June  */

IF H <0 THEN Jun2 = 0 AND I = SUM(H,Points_May);

ELSE IF H >= 0 THEN Jun2 = Points_Jun AND I = Points_May;

/* May  */

IF I <0 THEN May2 = 0 AND J = SUM(I,Points_Apr);

ELSE IF I >= 0 THEN May2 = Points_May AND J = Points_Apr;

/* Apr  */

IF J <0 THEN Apr2 = 0 AND K = SUM(J,Points_Mar);

ELSE IF J >= 0 THEN Apr2 = Points_Apr AND K = Points_Mar;

/* Mar  */

IF K <0 THEN Mar2 = 0 AND L = SUM(K,Points_Feb);

ELSE IF K >= 0 THEN Mar2 = Points_Mar AND L = Points_Feb;

/* Feb  */

IF L <0 THEN Feb2 = 0;

ELSE IF L >= 0 THEN Feb2 = Points_Feb;

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!

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
  • 5 replies
  • 629 views
  • 0 likes
  • 4 in conversation