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;
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;
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 |
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;
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;
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.
You shouldn't have a set statement (read a sas table) and input+datalines (read free text) statement together.
I suspect you want set.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: