topic Re: error in converting text date column d/mm/yyyy to SAS date column YYYYMMM in SAS Programming

error in converting text date column d/mm/yyyy to SAS date column YYYYMMM

Phil_from_PGA — Fri, 04 Aug 2017 01:00:07 GMT

Hello

Can anyone please help - I want to convert a text data column that has 1/mm/yyyy to a sas date column that has only yyyymmm, for example 1/09/2013 to 2013M09 (please see files attached)

libname homebase 'C:\Homebase';

proc print data=homebase.dummy1;

run;

data homebase.dummy2;

set homebase.dummy1;

sample_date = input(period,yymmdd10.);

format sample_date yymm10.;

run;

proc print data=homebase.dummy2;

Re: error in converting text date column d/mm/yyyy to SAS date column YYYYMMM

novinosrin — Fri, 04 Aug 2017 01:17:35 GMT

data have;
input period : ddmmyy10. (item_code patient_cat prescriptions) ($);
format period yymmd.;
datalines;
9/1/2013 03387G C0 4941
9/1/2013 03387G C1 5113
9/1/2013 03387G G1 644
9/1/2013 03387G G2 3097
9/1/2013 03387G R0 233
9/1/2013 03387G R1 186
9/1/2013 03390K C0 10
9/1/2013 03390K C1 18
9/1/2013 03390K G1 1
9/1/2013 03390K G2 62
9/1/2013 03391L C1 3
9/1/2013 03391L G2 4
9/1/2013 03393N C0 8
9/1/2013 03393N C1 296
9/1/2013 03393N C1 5
9/1/2013 03393N G1 1
9/1/2013 03393N G2 555
9/1/2013 03393N R0 1
9/1/2013 03393N R1 1
10/1/2013 03387G C0 112
10/1/2013 03387G C1 4
10/1/2013 03387G G1 7
10/1/2013 03387G G2 3000
10/1/2013 03387G R0 23
10/1/2013 03387G R1 0
10/1/2013 03390K C0 325556
10/1/2013 03390K C1 6
10/1/2013 03390K G1 3
10/1/2013 03390K G2 2
10/1/2013 03391L C1 44566
10/1/2013 03391L G2 323
10/1/2013 03393N C0 21
10/1/2013 03393N C1 1
10/1/2013 03393N C1 1
10/1/2013 03393N G1 9
10/1/2013 03393N G2 9
10/1/2013 03393N R0 7
10/1/2013 03393N R1 4
;

Re: error in converting text date column d/mm/yyyy to SAS date column YYYYMMM

Phil_from_PGA — Fri, 04 Aug 2017 02:12:02 GMT

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;

Re: error in converting text date column d/mm/yyyy to SAS date column YYYYMMM

RW9 — Fri, 04 Aug 2017 08:37:20 GMT

Please don't mark one question as correct, then ask another question. Correct answered questions generally don't get looked at again, and it can be confusing trying to find information through multiple posts. Once you are happy a question is answered, then mark it as correct, and then if you have further questions, start a new post.

Also note, when posting questions, follow the guidance below the Post buttong when submitting new questions:

Post test data in the form of a datastep, using the code window which is the {i} above the post. This keeps formatting and shows structure and data in a usable form. Office files are dangerous.

Its also a good idea to show example output required, and what problems you get in logs etc. if there are any.