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-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!

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.

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
  • 9 replies
  • 1375 views
  • 3 likes
  • 4 in conversation