how to calculate how many types of drug and how much cost dose a patient take and spend

[huangyu]

I want to know what wrong my code is.

my code:

---------------------------------------------

libname b "D:\DATA";
run;
proc sort data= b.ex;
by id;
proc sql;
create table work.md as
select drug,

expenses,
input (count,best.) as count
from b.ex;
quit;

-------------------------------------------

[Kurt_Bremser]

Please post the complete log of your code by copy/pasting it into a window opened with this button:

[huangyu]

1    libname b "D:\DATA";
NOTE: Libref B was successfully assigned as follows:
      Engine:        V9
      Physical Name: D:\DATA
2    run;

3    proc sort data= b.ex;
4    by id;

NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
      real time           3.81 sec
      cpu time            0.78 sec


5    proc sql;
6    create table work.md as
7    select
8    drug,
9    expenses,
10   input (count,best.) as count
11   from b.ex;
ERROR: The following columns were not found in the contributing tables: count.
12   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.16 sec
      cpu time            0.01 sec

[Kurt_Bremser]

input (count,best.) as count

This means that you try to convert a character variable to numeric, while keeping the name.

But here

ERROR: The following columns were not found in the contributing tables: count.

you are alerted to the fact that this variable does not exist in the input dataset.

Given the picture you posted, and your subject line, I guess you want the count of observations and sum of expenses per patient. This would be done in SQL like this:

proc sql;
create table md as
  select
    id,
    count(drug) as count,
    sum(expenses) as sum
  from b.ex
  group by id
;
quit;

[huangyu]

thank you

[huangyu]

I also wnat to ask how to use "retain + if first.VARIABLE,  if last.VARIABLE" to do the same thing?

[Kurt_Bremser · Accepted solution]

data md;
set b.ex;
by id;
retain count sum;
if first.id
then do;
  count = 1;
  sum = expenses;
end;
else do;
  count + 1;
  sum + expenses;
end;
keep id count sum;
run;

[huangyu]

thank you