topic Re: producing a summary using if and first. in SAS Programming

producing a summary using if and first.

mona4u — Mon, 26 Mar 2018 13:28:54 GMT

I'm experiencing problem using first. and if statement

this is the code that I'm using

proc sort data=&Queries.2; by status category; run;

data &Queries.5;
set &Queries.2;
by status category;
retain numcat 0;
if first.category and status="Open" then do;
numcat+1;
if last.category and status="Open"; end;
else cat=0;
cat=numcat;
keep status category cat numcat;
run;

and I'm not getting the result that I want I want to count the number of distinct category only when status="Open" but

what I'm getting here it the code start to count every thing after status="Open"

which I don't desire to have. I want unreasonable to be 0 in both cat and numcat

Obs	Status	Category	numcat	cat
38	Closed	Unblinded Data	0	0
39	Closed	Unblinded Data	0	0
40	Closed	Unblinded Data	0	0
41	Open	Missing Images	1	1
42	Open	Other	2	2
43	Unresolvable	Missing Images	2	2
44	Unresolvable	Missing Images	2	2
45	Unresolvable	Missing Images	2	2
46	Unresolvable	Missing Images	2	2
47	Unresolvable	Missing Images	2	2
48	Unresolvable	Missing Images	2	2
49	Unresolvable	Scan Region	2	2

Re: producing a summary using if and first.

ballardw — Fri, 23 Mar 2018 22:44:17 GMT

A couple suggestions:

Get rid of that 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950 it adds nothing to the information and is possibly misleading as to variable names and such.

Post less example data of identical rows

Closed Other 
Closed Other 
Closed Other 
Closed Other 
Closed Other 
Closed Other 
Closed Other 
Closed Other 
Closed Other 
Closed Other

could just as well be

Closed Other 
Closed Other 
Closed Other

to get the structure of data across.

And finally, show what the expected result would be. Showing undesired output and then saying "which I don't desire to have." Doesn't exactly clarify which records should have "0 in both cat and numcat" (and that string of useless numbers obfuscates the column headings.

Even better is to post example data from your data set as data Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.step code.

Re: producing a summary using if and first.

art297 — Fri, 23 Mar 2018 22:41:11 GMT

I agree with @ballardw! You don't provide enough info for anyone to know what you're trying to do. My initial guess is:

data want;
  set have;
  by status category;
  if first.category then numcat=0;
  if status eq 'Open' then numcat+1;
  cat=numcat;
  keep status category cat numcat;
run;

Art, CEO, AnalystFinder.com

Re: producing a summary using if and first.

mona4u — Mon, 26 Mar 2018 13:29:25 GMT

just edit it

Re: producing a summary using if and first.

mona4u — Mon, 26 Mar 2018 13:37:42 GMT

that the result that I got by your code

Obs	Status	Category	numcat	cat
41	Open	Missing Images	1	1
42	Open	Other	1	1
43	Open	Other	2	2
44	Unresolvable	Missing Images	0	0
45	Unresolvable	Missing Images	0	0
46	Unresolvable	Missing Images	0	0
47	Unresolvable	Missing Images	0	0
48	Unresolvable	Missing Images	0	0
49	Unresolvable	Missing Images	0	0
50	Unresolvable	Scan Region	0	0

but that what I aim for

Status	Category	numcat	cat
Open	Missing Images	1	1
Open	Other	2	2
Unresolvable	Missing Images	0	0
Unresolvable	Missing Images	0	0
Unresolvable	Missing Images	0	0
Unresolvable	Missing Images	0	0
Unresolvable	Missing Images	0	0
Unresolvable	Missing Images	0	0
Unresolvable	Scan Region	0	0

Re: producing a summary using if and first.

Tom — Mon, 26 Mar 2018 13:41:56 GMT

@mona4u wrote:

that the result that I got by your code

Obs Status Category numcat cat

41 Open Missing Images 1 1

42 Open Other 1 1

43 Open Other 2 2

44 Unresolvable Missing Images 0 0

45 Unresolvable Missing Images 0 0

46 Unresolvable Missing Images 0 0

47 Unresolvable Missing Images 0 0

48 Unresolvable Missing Images 0 0

49 Unresolvable Missing Images 0 0

50 Unresolvable Scan Region 0 0

but that what I aim for

Status Category numcat cat

Open Missing Images 1 1

Open Other 2 2

Unresolvable Missing Images 0 0

Unresolvable Missing Images 0 0

Unresolvable Missing Images 0 0

Unresolvable Missing Images 0 0

Unresolvable Missing Images 0 0

Unresolvable Missing Images 0 0

Unresolvable Scan Region 0 0

Why do you want to eliminate observation 42 but not eliminate observations 44 to 48?

If you just want one observations per BY group than add this statement to the bottom of the data step.

if last.category;

Re: producing a summary using if and first.

SASUser_22 — Mon, 26 Mar 2018 18:01:20 GMT

I don't think you have to use the first. and last. steps.

data test2;
set test1;
by status category;
retain numcat 0;

if status = 'Open' then numcat+1;
else numcat=0;
cat = numcat;
keep status category cat numcat;
run;

this will get you required output.