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
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;
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;
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;
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
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;
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
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;
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
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;
Thank you for your time & detail explanation Hai.kuo.
Now i understood clearly!!!!! new thing to learn ....
Have a great night
Thanks
Sam
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.