I have a table with 17598 rows. In this table there are numerous procedure codes that have various columns of data contained within. I am trying to group all these various columns of data by the procedure code. I have tried a proc sql group by the proc but that did not work. Then tried by the proc and all these columns of data and that did not work.
DATA HAVE | |||||||
PROC_CD | _80 | RT | AS | _51 | _59 | LT | _79 |
11981 | |||||||
11981 | DISTINCT PROCEDURAL SERVICE | ||||||
11981 | DISTINCT PROCEDURAL SERVICE | LEFT SIDE PROCEDURE | UNRELATED PROCEDURE BY SAME PHYSICIAN POST OPERATIVE | ||||
11981 | RIGHT SIDE PROCEDURE | DISTINCT PROCEDURAL SERVICE | UNRELATED PROCEDURE BY SAME PHYSICIAN POST OPERATIVE | ||||
11981 | RIGHT SIDE PROCEDURE | MULTIPLE SURGICAL PROCEDURES | DISTINCT PROCEDURAL SERVICE | ||||
11981 | RIGHT SIDE PROCEDURE | MULTIPLE SURGICAL PROCEDURES | DISTINCT PROCEDURAL SERVICE | LEFT SIDE PROCEDURE | |||
DATA NEED | |||||||
PROC_CD | _80 | RT | AS | _51 | _59 | LT | _79 |
11981 | RIGHT SIDE PROCEDURE | MULTIPLE SURGICAL PROCEDURES | DISTINCT PROCEDURAL SERVICE | LEFT SIDE PROCEDURE | UNRELATED PROCEDURE BY SAME PHYSICIAN POST OPERATIVE |
If you'll only have one record per you try something as follows:
proc sql;
create table want as
select proc, max(var1) as var1, max(var2) as var2, etc /*List all variables that you want to get the obs for*/
from have
group by proc;
quit;
so some clarification. It looks like you don't have one row that has the distinct combination of values across your ID.
In your example every row within an ID has the same value, will this always be the case?? aka if id number 11981 says "multiple surgical procedures" in row 1, will it always say that value into row 2,3,4, etc..?
If so, you can use a retain statement to pull down the variables by ID, and then distinctify on the ID where the entire row is not null. Tht would get you the distinct values across your ID.....
However if your ID had different data it would actually give you multiple rows per process.
I am not sure I understand, however if you mean does the column header with those descriptions contain distinct values in each, then yes.
_51 will always be multiple surgical procedures
_21 will always be prolonged evaluation and management
There are a total of 47 different ones. This proc_cd just happen to have only these. I am not sure what a retain statement is. I will try and find that somewhere online.
If you'll only have one record per you try something as follows:
proc sql;
create table want as
select proc, max(var1) as var1, max(var2) as var2, etc /*List all variables that you want to get the obs for*/
from have
group by proc;
quit;
Couldn't you get what you want by using update in a data step? e.g.,
data want;
update have(obs=0) have;
by proc_cd;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.