Sum across month date and recurring item_code

[Phil_from_PGA]

Many thanks, this works, now my problem is that I want to sum the number of prescriptions in each month by item_code

That is, there should be a final table that has columns period, item_code and prescriptions.

There should be rows of which the first rown is the column name, the second should be 01SEP2013 and the third should be 01OCT2013.

The observations in the cells should be the sum of prescriptions in each month by (unique) item_code  

	item_code	sum of prescriptions
1-Sep-13
1-Oct-13

Proc sql;

create table homebase.dummy3 as

select item_code, sum(prescriptions) as prescriptions_sum

from homebase.dummy2

group by item_code, period;

quit;

proc print data=homebase.dummy3;

run;

[Phil_from_PGA]

Sorry, I mean the following should be the result with the last column being the sum of prescriptions:

item_code	period	(No column name)
03387G	9/01/2013	14214
03387G	10/01/2013	3146
03390K	9/01/2013	91
03390K	10/01/2013	325567
03391L	9/01/2013	7
03391L	10/01/2013	44889
03393N	9/01/2013	867
03393N	10/01/2013	52

[ChrisNZ · Accepted solution]

Just add the wanted column:

Proc sql;

create table homebase.dummy3 as

select item_code, period, sum(prescriptions) as prescriptions_sum

from homebase.dummy2

group by item_code, period;

quit;

proc print data=homebase.dummy3;

run;

[Phil_from_PGA]

thanks for replying Chris. I ran the code but realise now there is something wrong in an earlier step where i create a precursor table called dummy2 and when I proc print the table it looks fine. However when I open it, the message I get is Data set has nil observations.

I've attached my original data which is imported to SAS as dummy1 which then is converted to dummy2 (to make the date column SAS based) and then dummy3 to try and do the sum of prescriptions

Here's my code:

Libname pointing to homebase folder;

libname homebase 'C:\Homebase';

* View SAS table called dummy1 ;

proc print data=homebase.dummy1;

run;

　

* this creates a new table and formats the period column to be a SAS DATE column;

data homebase.dummy2;

set homebase.dummy1;

input period : ddmmyy10. (item_code patient_cat prescriptions) ($);

format period yymmd.;

datalines;

run;

proc print data=homebase.dummy2;

run;

Proc sql;

create table homebase.dummy3 as

select item_code, period, sum(prescriptions) as prescriptions_sum

from homebase.dummy2

group by item_code, period;

quit;

proc print data=homebase.dummy3;

run;

[RW9]

Post your test data in the form of a datastep using a code window - its the {i} above the post area.  Office files are not safe to download off the net.

[ChrisNZ]

You shouldn't have a set statement (read a sas table) and input+datalines (read free text) statement together.

I suspect you want set.