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

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

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

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;

View solution in original post

9 REPLIES 9
slchen
Lapis Lazuli | Level 10

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
Ammonite | Level 13

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
Obsidian | Level 7

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
Ammonite | Level 13

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
Obsidian | Level 7

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
Onyx | Level 15

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
Obsidian | Level 7

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
Onyx | Level 15

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
Obsidian | Level 7

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

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

Have a great night

Thanks

Sam 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1973 views
  • 3 likes
  • 4 in conversation