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 |
@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;
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.
just edit it
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
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 |
@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;
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.