How do I flag obs 1 and 2 when condition is true

jakestat — Fri, 04 Feb 2022 16:48:23 GMT

I am trying to create a conditional flag variable count(cnt) for both the first and second observation rows within/or by 'class' criteria if the condition is true. The class criteria is by Region (_rg) State (st) Strata3 (str3). I am collapsing strata level 1 into strata level 2 where n<10 of the variable strataY (str3) and creating strataX (str4) such that strata X receives the value of 1 for both strata 1 and strata 2. an this case I am flagging strata 1 and strata 2 both.

proc summary data=b; class _rg st str3; var i691; output out=xx sum=; run;

data xx looks like this:
Obs _rg st str3 _TYPE_ _FREQ_ i691
218 1 6 0 7 1 .
219 1 6 1 7 22 167
220 1 6 3 7 11 2082
221 1 8 0 7 22 .
222 1 8 1 7 39 178
223 1 8 2 7 20 290
224 1 8 3 7 22 1112
225 1 8 4 7 21 11538
226 1 16 1 7 23 150
227 1 16 3 7 19 5543
228 1 20 0 7 26 .
229 1 20 1 7 32 118
230 1 20 2 7 31 471
231 1 20 3 7 31 1590
232 1 20 4 7 10 4633
233 1 30 0 7 3 .
234 1 30 1 7 19 82
235 1 30 2 7 15 252

data c;
set xx; by _rg st str3;
where _type_=7;
if first.st then do;
if first.st and _freq_<10 and str3 ne 0 then do;
retain cnt; cnt=1; end;
else cnt=.;
end;
if cnt=1 then str4=1;
else str4=str3;
keep _rg st str3 str4 _freq_ cnt;
run;

data C print: Notice state 53 didn't get a 1 looks like this:

Obs _rg st str3 _FREQ_ cnt str
1 1 6 0 1 . 0
2 1 6 1 22 . 1
3 1 6 3 11 . 3
4 1 8 0 22 . 0
5 1 8 1 39 . 1
6 1 8 2 20 . 2
7 1 8 3 22 . 3
8 1 8 4 21 . 4

44 1 48 0 1 . 0
45 1 48 1 124 . 1
46 1 48 2 45 . 2
47 1 48 3 24 . 3
48 1 48 4 10 . 4
49 1 49 1 13 . 1
50 1 49 2 10 . 2
51 1 53 0 6 . 0
52 1 53 1 9 . 1
53 1 53 2 14 . 2
54 1 53 3 11 . 3
55 1 56 0 3 . 0
56 1 56 1 13 . 1
57 1 56 2 16 . 2
58 1 56 4 13 . 4
59 1 99 1 4 1 1
60 1 99 2 10 1 1

I assume the problem is that strata level 1 or 0 does not always occur as a first.var condition. Maybe there is a completely different way to solve this. I'm a novice using someone's previously written code. Thank you.

Re: How do I flag obs 1 and 2 when condition is true

ballardw — Fri, 04 Feb 2022 17:54:59 GMT

Since your first data set looks like the output from proc mean/ summary you may want to go back a step or two and investigate the option COMPLETETYPES on the proc statement.

That might (since we have no date prior to your Data xx example) create all the levels of the strata variables you are looking to use.

From the documentation:

COMPLETETYPES

creates all possible combinations of class variables even if the combination does not occur in the input data set.

@jakestat wrote:

I am trying to create a conditional flag variable count(cnt) for both the first and second observation rows within/or by 'class' criteria if the condition is true. The class criteria is by Region (_rg) State (st) Strata3 (str3). I am collapsing strata level 1 into strata level 2 where n<10 of the variable strataY (str3) and creating strataX (str4) such that strata X receives the value of 1 for both strata 1 and strata 2. an this case I am flagging strata 1 and strata 2 both.

proc summary data=b; class _rg st str3; var i691; output out=xx sum=; run;

data xx looks like this:
Obs _rg st str3 _TYPE_ _FREQ_ i691
218 1 6 0 7 1 .
219 1 6 1 7 22 167
220 1 6 3 7 11 2082
221 1 8 0 7 22 .
222 1 8 1 7 39 178
223 1 8 2 7 20 290
224 1 8 3 7 22 1112
225 1 8 4 7 21 11538
226 1 16 1 7 23 150
227 1 16 3 7 19 5543
228 1 20 0 7 26 .
229 1 20 1 7 32 118
230 1 20 2 7 31 471
231 1 20 3 7 31 1590
232 1 20 4 7 10 4633
233 1 30 0 7 3 .
234 1 30 1 7 19 82
235 1 30 2 7 15 252

data c;
set xx; by _rg st str3;
where _type_=7;
if first.st then do;
if first.st and _freq_<10 and str3 ne 0 then do;
retain cnt; cnt=1; end;
else cnt=.;
end;
if cnt=1 then str4=1;
else str4=str3;
keep _rg st str3 str4 _freq_ cnt;
run;

data C print: Notice state 53 didn't get a 1 looks like this:

Obs _rg st str3 _FREQ_ cnt str
1 1 6 0 1 . 0
2 1 6 1 22 . 1
3 1 6 3 11 . 3
4 1 8 0 22 . 0
5 1 8 1 39 . 1
6 1 8 2 20 . 2
7 1 8 3 22 . 3
8 1 8 4 21 . 4

44 1 48 0 1 . 0
45 1 48 1 124 . 1
46 1 48 2 45 . 2
47 1 48 3 24 . 3
48 1 48 4 10 . 4
49 1 49 1 13 . 1
50 1 49 2 10 . 2
51 1 53 0 6 . 0
52 1 53 1 9 . 1
53 1 53 2 14 . 2
54 1 53 3 11 . 3
55 1 56 0 3 . 0
56 1 56 1 13 . 1
57 1 56 2 16 . 2
58 1 56 4 13 . 4
59 1 99 1 4 1 1
60 1 99 2 10 1 1

I assume the problem is that strata level 1 or 0 does not always occur as a first.var condition. Maybe there is a completely different way to solve this. I'm a novice using someone's previously written code. Thank you.

topic Re: How do I flag obs 1 and 2 when condition is true in SAS Programming

How do I flag obs 1 and 2 when condition is true

Re: How do I flag obs 1 and 2 when condition is true

COMPLETETYPES