topic grouping ids with result value in SAS Programming

grouping ids with result value

Dhana18 — Mon, 24 Feb 2020 18:42:31 GMT

Hi, need your help please.

data want;
set have;
input id test $ result $;
1 GCCX NEG
1 GCCXO NEG
1 GCCX POS
2 GCCX NEG
3 GCCXO POS
3 GCCXO NEG
3 GCCX NEG
4 GCCX NEG
5 GCCX POS
5 GCCX POS
RUN;

I would like to have the our put like this:
 id test $ result $;
1 GCCX POS
2 GCCX NEG
3 GCCX POS
4 GCGX NEG
5 GCCX POS

if an id has at least one positive then i wold like to have it count once as pos.
if an id has no pos only neg then count that id only once as neg.  it does ot matter if it is GCCX OR GCCXO.

Re: grouping ids with result value

PeterClemmensen — Mon, 24 Feb 2020 18:47:50 GMT

data have;
input id test $ result $;
datalines;
1 GCCX NEG
1 GCCXO NEG
1 GCCX POS
2 GCCX NEG
3 GCCXO POS
3 GCCXO NEG
3 GCCX NEG
4 GCCX NEG
5 GCCX POS
5 GCCX POS
;

data want;
   r='NEG';
   do until (last.id);
      set have;
      by id;
      if result='POS' then r='POS';
   end;
   result = r;
run;

Re: grouping ids with result value

Dhana18 — Mon, 24 Feb 2020 19:01:16 GMT

proc sort data=SURRGWED.Epidata_COMBINED_RESULT;
by ClientID;
RUN;
data want;
r='NEG';
do until (last.ID);
set have;
by ID;
if result='POS' then r='POS';
end;
result = r;
run;
proc freq data=want;
table RESULT*r/NOROW NOCOL NOPERCENT;
RUN;

it did not give me positive results. here is the put put.

result Neg

r neg 4839

Re: grouping ids with result value

PeterClemmensen — Mon, 24 Feb 2020 19:24:46 GMT

Seems like your actual ID variable is ClientID and not just ID

Re: grouping ids with result value

ghosh — Mon, 24 Feb 2020 19:29:07 GMT

Proc format;
   invalue resi
   'POS'=1
   'NEG'=0;
   value resv
   1='POS'
   0='NEG';
run;

data have;
input id test:$4. result $;
resn=input(result,resi.);
datalines;
1 GCCX NEG
1 GCCXO NEG
1 GCCX POS
2 GCCX NEG
3 GCCXO POS
3 GCCXO NEG
3 GCCX NEG
4 GCCX NEG
5 GCCX POS
5 GCCX POS
;
proc summary nway data=have(drop=result);
   class id;
   id test;
   var resn;
   output out=want (drop=_:)
   max=result;
 run;
proc print;
   format result resv.;
run;

Re: grouping ids with result value

Ksharp — Tue, 25 Feb 2020 05:02:11 GMT

data have;
input id test $ result $;
datalines;
1 GCCX NEG
1 GCCXO NEG
1 GCCX POS
2 GCCX NEG
3 GCCXO POS
3 GCCXO NEG
3 GCCX NEG
4 GCCX NEG
5 GCCX POS
5 GCCX POS
;

proc sql;
select id,min(test) as test,ifc(sum(result='POS'),'POS','NEG') as want
 from have
  group by id;
quit;