Hello SAS community I need help with the following. I am attempting to sort a data set of applications by 2 variables. One variable being the application status and the other being the application id. I would like to drop the observations containing a status of completed which fall under the same id. I have attempted using a proc sort with a nodup option but end up with a blank table for my output. Below is what I have so far. Thank you for your time!
proc sort data=set out=set1 nodup;
by id;
where status='completed'
run;
proc sort data = example
out = completed
nodupkey;
by id status;
where status = 'C';
run;
proc sort data = example
out = aborted
;
by id status;
where status = 'A';
run;
data want;
merge completed aborted;
by id status;
run;
Hi,
Your criteria are a little more complex than what your code accomplishes.
To start, you need to add a semicolon to end the where statement. You can also add the second variable, status, to your BY statement.
The WHERE statement exlcudes all records where the status is equal to 'completed', not sure if that's what you want because it doesn't guarantee that there is another record for this ID where the status is something other than 'completed'. And your criteria state you would like to "drop the observations containing a status of completed which fall under the same id", so that maybe something to look into, if there are IDs with two or more records where one is complete.
maybe this helps:
proc sort data=set (where=(status='completed')) out=set1 nodup;
by id;
run ;
This returned a blank dataset also. I am not sure why
Hi. These are identical since the WHERE statement also only applies to the input data set, not the output ...
proc sort data=x out=y nodup;
by id;
where status eq 'completed';
run;
proc sort data=x (where=(status eq 'completed')) out=y nodup;
by id;
where status eq 'completed';
run;
Please provide an example of an input dataset, preferably in the form of a data step that we can all execute, and wha the desired result for that example data is. The data should provide examples of all of the issues you are addressing.
DATA EXAMPLE;
INPUT ID STATUS;
DATALINES;
100 C
200 A
300 C
400 C
500 C
400 C
300 A
200 A
100 C
500 A
This is an example in a data step. I want to remove any duplicate statuses of completed. For instance id number 100 and 400 have a duplicate status of C(completed) I would like the new data set to present 100 and 400 once with the status completed. Id number 200 is duplicated but has a status of A(aborted). I would like to keep both of these obeservations. With the correct code the new data set would present as follows:
100 C
200 A
300 C
400 C
500 C
300 A
200 A
500 A
proc sort data = example
out = completed
nodupkey;
by id status;
where status = 'C';
run;
proc sort data = example
out = aborted
;
by id status;
where status = 'A';
run;
data want;
merge completed aborted;
by id status;
run;
Hi. Would you accept SORT + a DATA STEP as a solution ...
data example;
input id status :$1. @@;
datalines;
100 C 200 A 300 C 400 C 500 C 400 C 300 A 200 A 100 C 500 A
;
proc sort data=example;
by id;
run;
data want (drop=complete);
do until (last.id);
set example;
by id;
complete + (status eq 'C');
if status eq 'A' or complete eq 1 then output;
end;
complete = 0;
run;
data set WANT ...
Obs id status
1 100 C
2 200 A
3 200 A
4 300 C
5 300 A
6 400 C
7 500 C
8 500 A
Hi. On further thought, these two also work ...
data example;
input id status :$1. @@;
datalines;
100 C 200 A 300 C 400 C 500 C 400 C 300 A 200 A 100 C 500 A
;
* use SQL;
proc sql;
create table want as
select distinct * from example where status = 'C'
union all
select * from example where status = 'A'
order id, status desc;
quit;
* or, use SORT plus a simple data step;
proc sort data=example;
by id descending status;
run;
data want;
set example;
by id;
if (first.id and status eq 'C') or status eq 'A';
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: