topic Re: Vertical compress in SAS Procedures

Vertical compress

Greek — Wed, 13 May 2015 15:26:00 GMT

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!

Re: Vertical compress

Astounding — Wed, 13 May 2015 15:45:19 GMT

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.

Re: Vertical compress

data_null__ — Wed, 13 May 2015 15:56:01 GMT

Use MISSING function to avoid the type issue.

where=(not missing(date))

Re: Vertical compress

stat_sas — Wed, 13 May 2015 15:56:13 GMT

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;

Re: Vertical compress

data_null__ — Wed, 13 May 2015 16:06:46 GMT

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;