Converting to a Date

[itshere]

2 part questions, PLEASE HELP! 

I currently have 

01OCT2015:15:22:22 in my column.. I want to add a new column that will just say OCTOBER 

Im using SAS PROC SQL and need to creat another table.. 

So it would be rsubmit;

proc sql;

create table test1.new as 

.......

2)

I have a column that says CARS and and MONTHS ( that will be from the above query) 

One of the times I need to count the number of cars purchased for each months. 

and then i need another query that shows only the UNIQUE numbers of CARS for each months. 

So one with total (with dups) and other time with just Unique ones?

PLEASE HELP. Also using proc sql and need to create another table. 

[majdi_ka]

Hi,

1) Here's an example that you can execute

data _null_;
    month=put(datepart("01OCT2015:15:22:22"dt),worddatx9.);
    put month=;
run;

- 01OCT2015:15:22:22 is a datetime. To convert it to a date you can use datepart function.
- worddatw9. is a format that displays the date's month.

- put function creates a string based on the format applyed.

2) 

proc sql;
   create table want as
        select month
                 ,count(1) as nb_cars
                 ,count(distinct cars) as nb_distinct_cars
          from have
             group by month
;
quit;

Hope this helps !

[itshere]

For the first part.. you did 

(datepart("01OCT2015:15:22:22"dt)

but its the entire column thats like that so I would need to convert the entire column.

Also.. how do i leave that column and create a new one with the actual MONTH?

Thank you!! 

[majdi_ka · Accepted solution]

Here is an example that can help. the first data step is to simulate the data that you have. The second is to create the output that you wanted.

data have;
format date datetime21.;
input date datetime21. ;
cards;
01OCT2015:15:22:22
3OCT2015:15:22:22
01NOV2015:15:22:22
01MAR2015:15:22:22
01FEB2015:15:22:22
01SEP2015:15:22:22
01AUG2015:15:22:22
;
run;

data want;
	set have;
	month=put(datepart(date),worddatx9.);
run;

[itshere]

I used the second query and put in the column name in the "date"  and it didnt work.... 

how would i create a table rathe than overwrite the existing one?

[majdi_ka]

Can you please share your code ?

[itshere]

NOTE: Variable start_time is uninitialized.
month=.
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 17:15

[majdi_ka]

This is the log, the result of the execution of your code, and not the code. Can you the code you executed that you find in the editor ?

[itshere]

rsubmit;
data test1.new
month=put(datepart(start_time),worddatx9.);
put month=;
run;

[majdi_ka]

you forgot the set statement with the table containing your variable start_time.

[itshere]

but will it create a new column or overwrite the existing column?

[DeepakSwain]

Hi itshere,

I have make a simple try below assuming different type of car purchased per month. If you could provide more information about uniqueness, I may try to refine it further.

data test1;
format date datetime21.;
input  type $ date datetime21.  ;
cards;
a 01OCT2015:15:22:22
b 3OCT2015:15:22:22
a 11OCT2015:15:22:22
b 13OCT2015:15:22:22
c 11OCT2015:15:22:22
b 30OCT2015:15:22:22
a 01NOV2015:15:22:22
a 02NOV2015:15:22:22
b 03NOV2015:15:22:22
a 01MAR2015:15:22:22
b 02MAR2015:15:22:22
c 03MAR2015:15:22:22
;
run;

data test2;
	set test1;
	month=put(datepart(date),worddatx9.);
run;

proc sql;
   create table test3 as
        select month, type,count(type) as num_of_car_per_month
          from test2
             group by month, type
;
quit;