Hi
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?
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;
Please provide your dataset in a data step with datalines, so we can be sure about variable attributes and contents.
Like
data have;
infile datalines dlm="," dsd;
input test_center:$50. capacity;
datalines;
Test center Toronto,460
;
I have edited the question and table
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;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.