DATA Step, Macro, Functions and more

producing a summary using if and first.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 149
Accepted Solution

producing a summary using if and first.

[ Edited ]

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

 

 

 

ObsStatusCategorynumcatcat
38ClosedUnblinded Data00
39ClosedUnblinded Data00
40ClosedUnblinded Data00
41OpenMissing Images11
42OpenOther22
43UnresolvableMissing Images22
44UnresolvableMissing Images22
45UnresolvableMissing Images22
46UnresolvableMissing Images22
47UnresolvableMissing Images22
48UnresolvableMissing Images22
49UnresolvableScan Region22

 

 

 

 


Accepted Solutions
Solution
‎03-26-2018 10:45 AM
Super User
Super User
Posts: 7,932

Re: producing a summary using if and first.


@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;

View solution in original post


All Replies
Super User
Posts: 13,300

Re: producing a summary using if and first.

[ Edited ]

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-dat... 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.

Frequent Contributor
Posts: 149

Re: producing a summary using if and first.

just edit it 

PROC Star
Posts: 8,146

Re: producing a summary using if and first.

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

 

 

Frequent Contributor
Posts: 149

Re: producing a summary using if and first.

that the result that I got by your code 

ObsStatusCategorynumcatcat
41OpenMissing Images11
42OpenOther11
43OpenOther22
44UnresolvableMissing Images00
45UnresolvableMissing Images00
46UnresolvableMissing Images00
47UnresolvableMissing Images00
48UnresolvableMissing Images00
49UnresolvableMissing Images00
50UnresolvableScan Region00

 

 

but that what I aim for 

StatusCategorynumcatcat
OpenMissing Images11
OpenOther22
UnresolvableMissing Images00
UnresolvableMissing Images00
UnresolvableMissing Images00
UnresolvableMissing Images00
UnresolvableMissing Images00
UnresolvableMissing Images00
UnresolvableScan Region00
Solution
‎03-26-2018 10:45 AM
Super User
Super User
Posts: 7,932

Re: producing a summary using if and first.


@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;
Occasional Contributor
Posts: 6

Re: producing a summary using if and first.

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 141 views
  • 3 likes
  • 5 in conversation