DATA Step, Macro, Functions and more

Collapsing Missing Variables

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

Collapsing Missing Variables

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:

 

IDInitialCountFinalCount
10.
1..
1..
1..
1.0
23.
2..
2..
2..
2.0
30.
3..
3..
3.7

 

Here is what I want:

 

IDInitialCountFinalCount
100
230
307

 

Any help would be greatly appreciated! Thanks in advance! 


Accepted Solutions
Solution
‎11-17-2016 03:23 PM
Super User
Posts: 5,085

Re: Collapsing Missing Variables

[ Edited ]

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.

View solution in original post


All Replies
PROC Star
Posts: 552

Re: Collapsing Missing Variables

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

Contributor
Posts: 50

Re: Collapsing Missing Variables

Hi Draycut, Yes, that is how I setup the two variables.
PROC Star
Posts: 552

Re: Collapsing Missing Variables

ok. There u go Smiley Happy

 

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;
Solution
‎11-17-2016 03:23 PM
Super User
Posts: 5,085

Re: Collapsing Missing Variables

[ Edited ]

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.

PROC Star
Posts: 552

Re: Collapsing Missing Variables

Cool solutions @Astounding Smiley Happy

Super User
Posts: 10,500

Re: Collapsing Missing Variables

proc summary data=have nway;

   class id;

   var initialcount finalcount;

   output out=want (drop= _Smiley Happy max= ;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 248 views
  • 1 like
  • 4 in conversation