topic Re: How can I remove certain values of variable while other variable meets the condition? in SAS Programming

How can I remove certain values of variable while other variable meets the condition?

MonoLight — Tue, 18 Sep 2018 07:50:12 GMT

Hi there.

If I have a data like below,

Data have;
Input A1 A2 A3 A4;
Cards;
1 2 3 4
1 3 4 2
1 2 3 2
2 3 2 3
2 3 4 2
2 4 5 3
3 2 4 3
3 3 4 2
;
Run;

I'd like to make a condition that, if S1=1, delete S2's value which is in same row of S1=1.

So the result should be like this.

Data have2;
Input A1 A2 A3 A4;
Cards;
1 . 3 4
1 . 4 2
1 . 3 2
2 3 2 3
2 3 4 2
2 4 5 3
3 2 4 3
3 3 4 2
;
Run;

And please let me know the way I can keep the values of S2 where value of S1=1, but remove others.

It should be like this.

Data have3;
Input A1 A2 A3 A4;
Cards;
1 2 3 4
1 3 4 2
1 2 3 2
2 . 2 3
2 . 4 2
2 . 5 3
3 . 4 3
3 . 4 2
;
Run;

Thank you for the great help and advice in advance!

gamotte — Tue, 18 Sep 2018 08:21:43 GMT

Hello,

Delete A2 when A1=1 :

if A1=1 then A2=.;

if A1=1 then call missing(A2);

Delete A2 when A1 != 1 :

if A1 ne 1 then A2=.;

MonoLight — Tue, 18 Sep 2018 09:09:25 GMT

Thank you!

Should I use Data? or Sql? (I'm not familiar with Sql though)

gamotte — Tue, 18 Sep 2018 09:14:10 GMT

Data step is fine.

Data have;
Input A1 A2 A3 A4;

if A1=1 then A2=.;
Cards;
1 2 3 4
1 3 4 2
1 2 3 2
2 3 2 3
2 3 4 2
2 4 5 3
3 2 4 3
3 3 4 2
;
Run;

With proc sql you could do

proc sql;
    UPDATE have
    SET A2=.
    WHERE A1=1;
quit;

MonoLight — Tue, 18 Sep 2018 09:15:58 GMT

Wow, Thank you gamotte!

I really appreciate it!

I didn't know I can write condition before input.

Thank you again!

andreas_lds — Tue, 18 Sep 2018 09:17:31 GMT

@MonoLight wrote:

Thank you!

Should I use Data? or Sql? (I'm not familiar with Sql though)

A data-step. in my eyes sql-code is almost always ugly mess.

MonoLight — Tue, 18 Sep 2018 09:32:29 GMT

If I want to delete A2's values if A1 is not 1,

Can I just use this?

proc sql;
    UPDATE have
    SET A2=.
    WHERE A1 != 1;
quit;

And actually I'm trying to delete A2s if A1's value is certain characters.

It keeps making error with != ...

gamotte — Tue, 18 Sep 2018 09:41:57 GMT

"!=" is not a valid operator in SAS. Use "ne" or "^=".