BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tmcrouse
Calcite | Level 5

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_80RTAS_51_59LT_79
11981
11981 DISTINCT PROCEDURAL SERVICE
11981 DISTINCT PROCEDURAL SERVICELEFT SIDE PROCEDUREUNRELATED 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 PROCEDURESDISTINCT PROCEDURAL SERVICE
11981 RIGHT SIDE PROCEDURE MULTIPLE SURGICAL PROCEDURESDISTINCT PROCEDURAL SERVICELEFT SIDE PROCEDURE
DATA NEED
PROC_CD_80RTAS_51_59LT_79
11981 RIGHT SIDE PROCEDURE MULTIPLE SURGICAL PROCEDURESDISTINCT PROCEDURAL SERVICELEFT SIDE PROCEDUREUNRELATED PROCEDURE BY SAME PHYSICIAN POST OPERATIVE
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

4 REPLIES 4
Anotherdream
Quartz | Level 8

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.

tmcrouse
Calcite | Level 5


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.

Reeza
Super User

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;

art297
Opal | Level 21

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;

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!

What is Bayesian Analysis?

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.

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
  • 4 replies
  • 863 views
  • 0 likes
  • 4 in conversation