BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mona4u
Lapis Lazuli | Level 10

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

@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

6 REPLIES 6
ballardw
Super User

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.

mona4u
Lapis Lazuli | Level 10

just edit it 

art297
Opal | Level 21

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

 

 

mona4u
Lapis Lazuli | Level 10

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
Tom
Super User Tom
Super User

@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;
SASUser_22
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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