topic Advanced calculations in New SAS User

Advanced calculations

mmea — Wed, 14 Apr 2021 11:04:58 GMT

if I have a data set like this:

data have;
infile datalines dlm=",";
length Test_place $100 Method $20;
input Test_place $ method $ capacity;
datalines;
Test place Toronto,Normal,599
Test place Toronto,Close contact,23
Test place Copenhagen,Normal,699
Test place Copenhagen,Close contact,23
Test Place Thisted,Normal,159
Test Place Thisted,Close contact,15
;
run;

Test center	Method	Capacity
Test center Toronto	Normal	599
Test center Toronto	close contact	23
Test Center Copenhagen	Normal	699
Test Center Copenhagen	close contact	23
Test Center Thisted	Normal	159
Test Center Thisted	close contact	500

I want to make two new datasets out of this dataset (I have more observations in the real one)

The first dataset should contain all test center who has capacity over 400, but the matching "close contact" center should follow with, even though it can have below 400.

The second dataset should only contain test centers below 400 and the matching close contact center should follow with IF the matching close contact is below 400.

How can I do these statements?

Re: Advanced calculations

Kurt_Bremser — Wed, 14 Apr 2021 10:45:24 GMT

Please provide your dataset in a data step with datalines, so we can be sure about variable attributes and contents.

data have;
infile datalines dlm="," dsd;
input test_center:$50. capacity;
datalines;
Test center Toronto,460
;

Re: Advanced calculations

mmea — Wed, 14 Apr 2021 11:05:49 GMT

I have edited the question and table

Re: Advanced calculations

Kurt_Bremser — Wed, 14 Apr 2021 11:22:38 GMT

With that data, it can be done in a data step:

data have;
infile datalines dlm=",";
length Test_place $100 Method $20;
input Test_place $ method $ capacity;
datalines;
Test place Toronto,Normal,599
Test place Toronto,Close contact,23
Test place Copenhagen,Normal,699
Test place Copenhagen,Close contact,23
Test Place Thisted,Normal,159
Test Place Thisted,Close contact,15
;

data
  over400
  under400
;
set have;
by test_place notsorted;
retain cap;
if first.test_place then cap = capacity;
if cap ge 400
then output over400;
else output under400;
drop cap;
run;

It's important that the "Normal" observation always comes first in a group. If it does not, a merge with itself will work:

proc sort data=have;
by test_place;
run;

data
  over400
  under400
;
merge
  have
  have (
    in=o400
    rename=(method=_m capacity=_cap)
    where=(_m = "Normal" and _cap ge 400)
  )
;
by test_place;
if o400
then output over400;
else output under400;
drop _:;
run;