Help using Base SAS procedures

Getting data on 1 line

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Getting data on 1 line

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

Accepted Solutions
Solution
‎10-25-2013 01:24 PM
Super User
Posts: 17,784

Re: Getting data on 1 line

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


All Replies
Super Contributor
Posts: 418

Re: Getting data on 1 line

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.

Contributor
Posts: 69

Re: Getting data on 1 line


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.

Solution
‎10-25-2013 01:24 PM
Super User
Posts: 17,784

Re: Getting data on 1 line

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;

PROC Star
Posts: 7,360

Re: Getting data on 1 line

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 182 views
  • 0 likes
  • 4 in conversation