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

I have a dataset which looks like this.

data have;
infile datalines truncover;
input type $ quarter phase counter percent avg std;
datalines;
XX-A 202001 1 30 20 20 1
XX-A 202002 1 50 2 20 1
XX-A 202003 1 60 2 20 2
XX-A 202004 1 50 10 20 2
XX-B 202001 2 30 4 30 3
XX-B 202002 2 60 4 30 3
XX-B 202003 2 70 1 30 3
XX-B 202004 2 20 3 30 3

I want to create two new columns named STATUS1 and STATUS05 based on the following if/case statements:

(for COLUMN STATUS1)
If count is < (avg + std * 1) STATUS1 = 'green'

if count > (avg + std * 1) and < (avg +std * 2) STATUS1 = 'yellow'

if count > (avg + std * 2) and (avg + std * 3) STATUS1 = 'orange'
if count > (avg + std * 3) STATUS1 = 'red'

(for COLUMN STATUS05)

If count is < (avg + std * 0.5)  STATUS05 = 'green'

if count > (avg + std * 0.5) and < (avg +std * 2) STATUS05 = 'yellow'

if count > (avg + std * 1) and (avg + std * 3) STATUS05 = 'orange'
if count > (avg + std * 1.5) STATUS05 = 'red'

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

Although I suspect you already know this, your statements in SAS need to end with a semicolon.

 

You also need a THEN in each statement, and there are a few other syntax errors you need to fix.

 

Here is correct code:

 

if count <= (avg + std * 1) then STATUS1 = 'green';
else if count > (avg + std * 1) and count <= (avg +std * 2) then STATUS1 = 'yellow';
else if count > (avg + std * 2) and count <= (avg + std * 3) then STATUS1 = 'orange';
else if count > (avg + std * 3) then STATUS1 = 'red';

 

You might also want to include the following BEFORE the above code, as it will prevent truncation of the values of STATUS1.

 

length status1 $ 6;
--
Paige Miller

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

Although I suspect you already know this, your statements in SAS need to end with a semicolon.

 

You also need a THEN in each statement, and there are a few other syntax errors you need to fix.

 

Here is correct code:

 

if count <= (avg + std * 1) then STATUS1 = 'green';
else if count > (avg + std * 1) and count <= (avg +std * 2) then STATUS1 = 'yellow';
else if count > (avg + std * 2) and count <= (avg + std * 3) then STATUS1 = 'orange';
else if count > (avg + std * 3) then STATUS1 = 'red';

 

You might also want to include the following BEFORE the above code, as it will prevent truncation of the values of STATUS1.

 

length status1 $ 6;
--
Paige Miller
Andalusia
Obsidian | Level 7
Oke and where is the STATUS05? I want STATUS1 and STATUS05 in one view
PaigeMiller
Diamond | Level 26

@Andalusia wrote:
Oke and where is the STATUS05? I want STATUS1 and STATUS05 in one view

I didn't show code for STATUS05. I expected you would be able to see what changes were made to the STATUS1 code, and make the same changes to the STATUS05 code.

--
Paige Miller
ballardw
Super User

@Andalusia wrote:

I have a dataset which looks like this.

data have;
infile datalines truncover;
input type $ quarter phase counter percent avg std;
datalines;
XX-A 202001 1 30 20 20 1
XX-A 202002 1 50 2 20 1
XX-A 202003 1 60 2 20 2
XX-A 202004 1 50 10 20 2
XX-B 202001 2 30 4 30 3
XX-B 202002 2 60 4 30 3
XX-B 202003 2 70 1 30 3
XX-B 202004 2 20 3 30 3

I want to create two new columns named STATUS1 and STATUS05 based on the following if/case statements:

(for COLUMN STATUS1)
If count is < (avg + std * 1) STATUS1 = 'green'

if count > (avg + std * 1) and < (avg +std * 2) STATUS1 = 'yellow'

if count > (avg + std * 2) and (avg + std * 3) STATUS1 = 'orange'
if count > (avg + std * 3) STATUS1 = 'red'

(for COLUMN STATUS05)

If count is < (avg + std * 0.5)  STATUS05 = 'green'

if count > (avg + std * 0.5) and < (avg +std * 2) STATUS05 = 'yellow'

if count > (avg + std * 1) and (avg + std * 3) STATUS05 = 'orange'
if count > (avg + std * 1.5) STATUS05 = 'red'

 


You were almost there but have several import issues. First all statements end in a semicolon.

Second you have to have the value in the right relationship for comparison. I am not aware of any programming language the "distributes" a variable as you try in the second and third statement. SAS will allow you to place the value between comparison operators:

Third you need a THEN after the comparison(s) to tell SAS what to do when the condition is true.

Fourth, the length of character values will be set with the first value used if you do not set it prior. So yours would have been 5 characters long and truncated values for "yellow" and "orange" to "yello" and "orang" because the first value for status "green" set length to 5.

And last you need another IF in the last comparison.

 

length status1 $ 6;
If count is < (avg + std * 1) THEN STATUS1 = 'green';
else if (avg + std * 1) < count  < (avg +std * 2) Then STATUS1 = 'yellow';
else if   (avg + std * 2)< count <(avg + std * 3) then STATUS1 = 'orange';
else if count > (avg + std * 3) Then STATUS1 = 'red' ;
Andalusia
Obsidian | Level 7
Where is STATUS05?

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 562 views
  • 4 likes
  • 3 in conversation