Vertical compress

[Greek]

Good morning,

I am trying to compress some data vertically and get rid of gaps (See example below). Do you have a procedure in mind that could help me?

What I have:

Date	cases	States
2002
	8
		Tennessee
1995
	11
		Wisconsin
2002
	14
		New York

What I need:

Date	cases	States
2002	8	Tennessee
1995	11	Wisconsin
2002	14	New York

Thank you!

[Astounding · Accepted solution]

The right approach depends on how confident you are in the structure of your data.  If you are absolutely certain that there are always groups of 3 observations, and absolutely sure that each observation contains a single non-missing value, the answer would be simple:

data want;

set have (keep=date where=(date > .));

set have (keep=cases where=(cases > .));

set have (keep=state where=(state > ' '));

run;

You might have to adjust the WHERE clauses, depending on whether DATE and CASES are character or numeric.

But if there could be other patterns of "present" vs. "missing" data values, a more complex approach would be needed.

Good luck.

[data_null__]

Use MISSING function to avoid the type issue.

where=(not missing(date))

[stat_sas]

data have;

set have;

if not missing(date) then n+1;

run;

proc stdize data=have reponly out=want;

var date cases;

by n;

run;

data need(drop=n);

set want;

by n;

if last.n;

run;

[data_null__]

If you're allowed to create an ID then why not just use HAVE as transaction file.

data have;
   infile cards missover;
   input Date cases States &$16.;
   cards;
2002    
. 8  
. . Tennessee
1995    
. 11 
. . Wisconsin
2002    
. 14 
. . New York
;;;;
   run;
data have;
   set have;
   if not missing(date) then n+1;
   run;

data have;
   update have(obs=0) have;
   by n;
   run;
proc print;
   run;