pick 0 and 1

[sam369]

Hi Experts,

I have following have dataset, where i need to do is if i have same grp with 0 & 1 , pick that grp need to assign val variable is - to 1 (-1)

In the below example 100 xxx, 100 aaa

data have;

input id grp1 $ val $;

cards;

100   xxxx    0

100   xxxx    1

100   yyyy    .33

100   yyyy    .67

100   zzzz    .5

100   zzzz    .5

100   aaaa   1

100   aaaa   0

;

run;

data want;

input id grp1 $ val;

cards;

100   xxxx     0

100   xxxx    -1

100   yyyy    .33

100   yyyy    .67

100   zzzz    .5

100   zzzz    .5

100   aaaa   -1

100   aaaa    0

;

run;

Thanks

Sam

[slchen]

proc sort data=have;

by id grp1 descending val;

run;

data want;

   merge have have(firstobs=2 rename=(id=_id grp1=_grp1 val=_val));

   if id=_id and grp1=_grp1 and val in (0,1) and _val in (0,1) then val=-val;

   drop _:;

run;

[stat_sas]

proc sql;

create table want as

select *,case when sum(val in (1,0))>1 and val=1 then val*-1 else val end as val1 from have

group by id,grp1;

quit;

[sam369]

Thank ,it worked for my sample data, but i have a another grp which i missed. Not only 0 & 1 if any of the grp have val 0 then the need to assign another val value to negative too

in the below case , xxx, aaaa, bbbb, cccc val values should be -val

data have;

input id grp1 $ val $;

cards;

100   xxxx    0

100   xxxx    1

100   yyyy    .33

100   yyyy    .67

100   zzzz    .5

100   zzzz    .5

100   aaaa   1

100   aaaa   0

100   bbbb   0.63

100   bbbb   0

100   cccc    0

100   cccc    .4

100   dddd    1

100   dddd    1

100   eeee    0

100  eeee     0

;

run;

data want;

input id grp1 $ val;

cards;

100   xxxx     0

100   xxxx    -1

100   yyyy    .33

100   yyyy    .67

100   zzzz    .5

100   zzzz    .5

100   aaaa   -1

100   aaaa    0

100   bbbb   -0.63

100   bbbb   0

100   cccc    0

100   cccc    -.40

100   dddd    1

100   dddd    1

100   eeee    0

100  eeee     0

;

Thanks

Sam

Message was edited by: peda sam

[stat_sas · Accepted solution]

A small modification in the above code should produce the desired results. Please try this.

proc sql;

create table want as

select *,case when sum(val in (1,0))>=1 then val*-1 else val end as val1 from have

group by id,grp1;

quit;

[sam369]

Hi Stat,

thank you so much for your quick response. i have added one more to have group according to my real data

data have;

input id grp1 $ trt $ val ;

cards;

100   xxxx  x  0

100   xxxx  y  1

100   yyyy  x  .33

100   yyyy  y  .67

100   zzzz  x  .5

100   zzzz  y  .5

100   aaaa  x 1

100   aaaa  y 0

100   bbbb  x 0.63

100   bbbb  y 0

100   cccc  x  0

100   cccc   y .4

100   dddd  x  1

100   dddd  y  1

100   eeee  x  0

100  eeee   y  0

100  ffff   x  .67

100  ffff   y  1

;

run;

proc sql;

create table want as

select *,case when sum(val in (1,0))>=1  & sum(val) ne 2 then val*-1 else val end as val1 from have

group by id,grp1;

quit;

output:

want1:

id    grp1 trt  val  val1

100   xxxx  x  0  0

100   xxxx  y  1  -1

100   yyyy  x  .33 .33

100   yyyy  y  .67 .67

100   zzzz  x  .5   .5

100   zzzz  y  .5   .5

100   aaaa  x 1    -1

100   aaaa  y 0     0

100   bbbb  x 0.63  -.63

100   bbbb  y 0     0

100   cccc  x  0   0

100   cccc   y .4  -.4

100   dddd  x  1   1

100   dddd  y  1   1

100   eeee  x  0   0

100  eeee   y  0   0

100  ffff   x  .67  .67

100  ffff   y  1   1

;

Is there any way to pick only values having 0 & (any value) for individual grp

i mean another dataset

want2:

100   xxxx  x  0

100   xxxx  y  1

100   aaaa  x 1

100   aaaa  y 0

100   bbbb  x 0.63

100   bbbb  y 0

100   cccc  x  0

100   cccc   y .4

;

run;

Thanks

Sam

[Haikuo]

stat@sas 's solution is on the right track, the reason being, I have to say, is you switched your original requirements on the course. A small tweak on stat@sas 's code will work for you, mind you to give a try. The following is equivalent, but it takes more to understand:

proc sql;

create table want as

select *, (-1)**(sum(val = 0)>0)*val as val1 from have

group by id,grp1;

quit;

[sam369]

HI Hai.kuo,

Thank you so much It worked!!!!

could you please explain in detail the below part... i am not aware of **

"(-1)**(sum(val = 0)>0)*val"

Thanks

Sam

[Haikuo]

if there is a (val=0) in the group, then sum(val=0) should great than 0, the Boolean value of (sum(val=0) >0) is 1 (true statement). we put (sum(val=0) >0) as the power of (-1), this is reflect a conditional choice, when there is (val=0) in the group, then (-1) **1= -1, val gets to flip sign, if not, (-1) ** 0 =1, val gets to stay the same.

If you go back to the code by stat@sas, it only takes the following tweak to do the same thing, and is a lot easier to understand:

proc sql;

create table want as

select *,case when sum(val = 0)>=1 then val*-1 else val end as val1 from have

group by id,grp1;

quit;

[sam369]

Thank you for your time & detail explanation Hai.kuo.

Now i understood clearly!!!!! new thing to learn ....

Have a great night

Thanks

Sam