Help using Base SAS procedures

Vertical compress

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

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: 5,071

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.

View solution in original post


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

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.

Respected Advisor
Posts: 3,777

Re: Vertical compress

Use MISSING function to avoid the type issue.

where=(not missing(date))

Trusted Advisor
Posts: 1,204

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;

Respected Advisor
Posts: 3,777

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.

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

Discussion stats
  • 4 replies
  • 242 views
  • 6 likes
  • 4 in conversation