Collapsing Missing Variables

[hwangnyc]

Hi Everyone,

I have a set of data in long form that contains missing data. I'd like to collapse by the ID and have 1 row per ID.

Here is what I have:

ID	InitialCount	FinalCount
1	0	.
1	.	.
1	.	.
1	.	.
1	.	0
2	3	.
2	.	.
2	.	.
2	.	.
2	.	0
3	0	.
3	.	.
3	.	.
3	.	7

Here is what I want:

ID	InitialCount	FinalCount
1	0	0
2	3	0
3	0	7

Any help would be greatly appreciated! Thanks in advance! 

[PeterClemmensen]

Can you be sure that your data always has only 1 nonmissing obervation for InitialCount and 1 nonmissing obervation for FinalCount for each ID?

[hwangnyc]

Hi Draycut, Yes, that is how I setup the two variables.

[PeterClemmensen]

ok. There u go 🙂

data have;
input ID	InitialCount FinalCount;
datalines;
1	0	.
1	.	.
1	.	.
1	.	.
1	.	0
2	3	.
2	.	.
2	.	.
2	.	.
2	.	0
3	0	.
3	.	.
3	.	.
3	.	7
;

proc sort data = have;
   by ID;
run;

data help;
   set have;
   by ID;
   if first.ID or last.ID then output;
run;

data want(drop = lag);
   set help;
   by ID;

   lag = lag(InitialCount);

   if last.ID then do;
      InitialCount = lag;
      output;
   end;
run;

[Astounding · Accepted solution]

The easiest program might be:

proc summary data=have nway;

  by id;

  var InitialCount FinalCount;

  output out=want (drop=_type_ _freq_) max=;

run;

You can use a more complex program that doesn't require you to specify the variable names:

data want;

update have (obs=0) have;

by id;

run;

For now, I suspect that learning more about PROC SUMMARY would be useful.

[PeterClemmensen]

Cool solutions @Astounding 🙂

[ballardw]

proc summary data=have nway;

   class id;

   var initialcount finalcount;

   output out=want (drop= _:) max= ;

run;