topic Re: pick 0 and 1 in SAS Procedures

pick 0 and 1

sam369 — Wed, 08 Jul 2015 22:33:47 GMT

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 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 aaaa -1

100 aaaa 0

;

run;

Thanks

Sam

Re: pick 0 and 1

slchen — Wed, 08 Jul 2015 22:54:51 GMT

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;

Re: pick 0 and 1

stat_sas — Wed, 08 Jul 2015 23:10:14 GMT

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;

Re: pick 0 and 1

sam369 — Wed, 08 Jul 2015 23:30:33 GMT

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 aaaa 1

100 aaaa 0

100 bbbb 0.63

100 bbbb 0

100 cccc 0

100 cccc .4

100 dddd 1

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 aaaa -1

100 aaaa 0

100 bbbb -0.63

100 bbbb 0

100 cccc 0

100 cccc -.40

100 dddd 1

100 eeee 0

;

Thanks

Sam

Message was edited by: peda sam

Re: pick 0 and 1

stat_sas — Thu, 09 Jul 2015 00:20:42 GMT

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;

Re: pick 0 and 1

sam369 — Thu, 09 Jul 2015 00:55:46 GMT

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

Re: pick 0 and 1

Haikuo — Thu, 09 Jul 2015 01:28:46 GMT

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;

Re: pick 0 and 1

sam369 — Thu, 09 Jul 2015 01:37:11 GMT

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

Re: pick 0 and 1

Haikuo — Thu, 09 Jul 2015 01:51:47 GMT

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;

Re: pick 0 and 1

sam369 — Thu, 09 Jul 2015 01:57:53 GMT

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

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

Have a great night

Thanks

Sam