topic Re: Collapsing Missing Variables in SAS Programming

Collapsing Missing Variables

hwangnyc — Thu, 17 Nov 2016 19:33:26 GMT

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!

Re: Collapsing Missing Variables

PeterClemmensen — Thu, 17 Nov 2016 19:36:52 GMT

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

Re: Collapsing Missing Variables

hwangnyc — Thu, 17 Nov 2016 19:43:09 GMT

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

Re: Collapsing Missing Variables

PeterClemmensen — Thu, 17 Nov 2016 19:56:57 GMT

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;

Re: Collapsing Missing Variables

Astounding — Thu, 17 Nov 2016 20:02:28 GMT

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.

Re: Collapsing Missing Variables

ballardw — Thu, 17 Nov 2016 20:02:01 GMT

proc summary data=have nway;

class id;

var initialcount finalcount;

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

run;

Re: Collapsing Missing Variables

PeterClemmensen — Thu, 17 Nov 2016 20:04:10 GMT

Cool solutions @Astounding 🙂