topic Re: Summarizing data in New SAS User

Summarizing data

ppl — Thu, 24 Mar 2022 23:47:51 GMT

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

2 1

2 3

3 0

How it should look like

ID Treatment(Dichotomous)

1 1

2 1

3 0

Thank you 🙂

Re: Summarizing data

tarheel13 — Fri, 25 Mar 2022 00:00:45 GMT

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;

Re: Summarizing data

mkeintz — Fri, 25 Mar 2022 00:25:12 GMT

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.

Re: Summarizing data

Ksharp — Fri, 25 Mar 2022 12:12:58 GMT

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;