Summarizing data

[ppl]

Hi,

I have a dataset, I want to select the IDs who have had treatment (0) vs those who have not(>0) and create. The summary should have only one ID per subject.

The below is my data.

ID treatment

1 0

1 1

1 1

2 1

2 3

2 3

3 0

3 0

3 0

How it should look like

ID   Treatment(Dichotomous)

1     1

2     1

3     0

Thank you 🙂

[tarheel13 · Accepted solution]

next time, please post your data as datalines. 

data have;
input ID treatment;
datalines;
1 0
1 1
1 1
2 1
2 3
2 3
3 0
3 0
3 0
;
proc print;
run;

data have2;
	set have;
	trt=treatment > 0;
run;

proc sort data=have2;
	by id descending treatment;
run;

data want;
	set have2;
	by id descending treatment;
	if first.id;
run;

[mkeintz]

For each ID, read all the treatment>0 cases followed by all the without-treatment cases.  Then just keep the first obs for each id and test its TREATMENT variable against 0.

data have;
input ID treatment;
datalines;
1 0
1 1
1 1
2 1
2 3
2 3
3 0
3 0
3 0
run;
data want (drop=treatment);
  set have (where=(treatment>0)) have (where=(treatment=0));
  by id;
  if first.id;
  trt_dummy=(treatment>0);
run;

This works if the data are already sorted by ID.  But it doesn't matter what the order is within each ID.

[Ksharp]

data have;
input ID treatment;
cards;
1 0
1 1
1 1
2 1
2 3
2 3
3 0
3 0
3 0
;

proc sql;
create table want as
select id,sum(treatment>0) ne 0 as flag
 from have 
  group by id;
quit;