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!
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.
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.
Use MISSING function to avoid the type issue.
where=(not missing(date))
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;
If you're allowed to create an ID then why not just use HAVE as transaction file.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.