BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Phil_from_PGA
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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
;

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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
;

Phil_from_PGA
Calcite | Level 5

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 869 views
  • 0 likes
  • 3 in conversation