Solved
Contributor
Posts: 48

# Vertical compress

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:

DatecasesStates
2002
8
Tennessee
1995
11
Wisconsin
2002
14
New York

What I need:

DatecasesStates
20028Tennessee
199511Wisconsin
200214New York

Thank you!

Accepted Solutions
Solution
‎05-13-2015 11:45 AM
Super User
Posts: 6,774

## Re: Vertical compress

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.

All Replies
Solution
‎05-13-2015 11:45 AM
Super User
Posts: 6,774

## Re: Vertical compress

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.

Posts: 3,852

## Re: Vertical compress

Use MISSING function to avoid the type issue.

where=(not missing(date))

Posts: 1,270

## Re: Vertical compress

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;

Posts: 3,852

## Re: Vertical compress

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;
🔒 This topic is solved and locked.