DATA Step, Macro, Functions and more

Sum across month date and recurring item_code

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Sum across month date and recurring item_code

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_codesum 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;


Accepted Solutions
Solution
‎08-04-2017 02:44 AM
PROC Star
Posts: 1,760

Re: Sum across month date and recurring item_code

Posted in reply to Phil_from_PGA

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;

View solution in original post


All Replies
Occasional Contributor
Posts: 15

Re: Sum across month date and recurring item_code

Posted in reply to 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

Solution
‎08-04-2017 02:44 AM
PROC Star
Posts: 1,760

Re: Sum across month date and recurring item_code

Posted in reply to Phil_from_PGA

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;

Occasional Contributor
Posts: 15

Re: Sum across month date and recurring item_code

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;

Super User
Super User
Posts: 7,955

Re: Sum across month date and recurring item_code

Posted in reply to Phil_from_PGA

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.

PROC Star
Posts: 1,760

Re: Sum across month date and recurring item_code

Posted in reply to Phil_from_PGA

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

 

I suspect you want set.

 

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 196 views
  • 0 likes
  • 3 in conversation