BookmarkSubscribeRSS Feed
VRD
Calcite | Level 5 VRD
Calcite | Level 5

I have 3 variables

s.no        cond1    cond2    cond3

1              4          8               min

2              3          0    

3              -1          8

4               .          1               min

5             0           -2   

i want to have multiple conditions.

if cond1 and cond2 is less than 0 and cond3 is missing then newvar=yes

if cond1 and cond2 is gt 0 and cond3 is missing then newvar=no

if cond 1 or cond2 is missing then newvar=missing.

11 REPLIES 11
stat_sas
Ammonite | Level 13

Here is my try

data have;

infile datalines missover;

input s_no   cond1   cond2    cond3 $;

datalines;

1              4         8     min

2              3         0    

3             -1         8

4              .         1     min

5              0        -2   

;

data want;

length newvar $ 5;

set have;

if cond1<0 and cond2<0 and cond3=' ' then newvar='yes';

else if cond1>0 and cond2>0 and cond3=' ' then newvar='no';

else if cond1=. and cond2=. then newvar=' ';

run;

ballardw
Super User

Missing is always less than 0 so you probably want:

if .< cond1 < 0 and . < cond2< 0 for the first comparison

and

else if cond1 = . or cond2=. then ...

What do you want to do if one of cond1 is <0 and the other is > 0?

Or exactly = 0 since your data has 2 examples of that. You may want LE or GE instead of LT or GT in one or more of your cases.

SKK
Calcite | Level 5 SKK
Calcite | Level 5

Hi.. Here's something with Proc SQl.. I manipulated your input data a little bit.. to get expected output..,

data have;
infile datalines missover;
input sno cond1 cond2 cond3 $;
datalines;
1              4          8              
2              .          .    
3              -1          8
4              .          1               min
5             -1           -2   
;
run;


PROC SQL;
CREATE TABLE WANT AS
SELECT *,
CASE WHEN COND1=. AND COND2=. THEN "MISSING"
WHEN COND1 LT 0 AND COND2 LT 0 AND COND3 =" " THEN "YES"
WHEN COND1 GT 0 AND COND2 GT 0 AND COND3= " " THEN "NO"
ELSE "UNKNOWN" END AS NEWVAR LENGTH=10
FROM HAVE;
QUIT;

VRD
Calcite | Level 5 VRD
Calcite | Level 5

i want like this.

if cond1 and cond2 is not missing and less than or equal to 0 and cond3 is missing than as yes.

if cond1 and cond2 is not missing and gt 0  than no

if cond1 or cond 2 is missing than as missing.

SKK
Calcite | Level 5 SKK
Calcite | Level 5

Try this , it works fine for ur condition and i have also added another value "Unknown" for data which does not meet any of the conditions

PROC SQL;

CREATE TABLE WANT AS

SELECT *,

CASE WHEN COND1=. AND COND2=. THEN "MISSING"

WHEN COND1 LE 0 AND COND2 LE 0 AND COND3 =" " THEN "YES"

WHEN COND1 GT 0 AND COND2 GT 0 AND COND3= " " THEN "NO"

/*ELSE "UNKNOWN"*/ END AS NEWVAR LENGTH=10

FROM HAVE;

QUIT;

VRD
Calcite | Level 5 VRD
Calcite | Level 5

No, This case will not work as  . will be less than 0 and it will consider as yes even cond2 ios missing which should not be the case

SKK
Calcite | Level 5 SKK
Calcite | Level 5

Try now

PROC SQL;

CREATE TABLE WANT AS

SELECT *,

CASE WHEN COND1=. OR COND2=. THEN "MISSING"

WHEN COND1 LE 0 AND COND2 LE 0 AND COND3 =" " THEN "YES"

WHEN COND1 GT 0 AND COND2 GT 0 AND COND3= " " THEN "NO"

/*ELSE "UNKNOWN"*/ END AS NEWVAR LENGTH=10

FROM HAVE;

QUIT;

Message was edited by: SK K

mfab
Quartz | Level 8

Well, I guess line 3 falls under the 'undefined' or 'unknown'. This just needs to be uncommented in the SQL-Statement above.

The original post did not specify what to do with such data. 🙂

Kurt_Bremser
Super User

Out of experience, I DO NOT LIKE possible conditions that are not covered in the code, they tend to come back and bite one in the ass Smiley Happy

So setting newvar explicitly to "unknown" is good practice. Just relying on empty (missing) values because they happen on their own may lead to unnecessary expensive debugging later on.

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
  • 11 replies
  • 1435 views
  • 0 likes
  • 6 in conversation