topic Re: Converting to a Date in SAS Programming

Converting to a Date

itshere — Wed, 22 Jun 2016 14:27:01 GMT

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

.......

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.

Re: Converting to a Date

majdi_ka — Wed, 22 Jun 2016 14:49:36 GMT

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.

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 !

Re: Converting to a Date

itshere — Wed, 22 Jun 2016 14:56:59 GMT

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!!

Re: Converting to a Date

majdi_ka — Wed, 22 Jun 2016 15:14:02 GMT

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;

Re: Converting to a Date

itshere — Wed, 22 Jun 2016 15:18:33 GMT

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?

Re: Converting to a Date

majdi_ka — Wed, 22 Jun 2016 15:22:43 GMT

Can you please share your code ?

Re: Converting to a Date

itshere — Wed, 22 Jun 2016 15:29:17 GMT

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

Re: Converting to a Date

majdi_ka — Wed, 22 Jun 2016 15:31:49 GMT

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 ?

Re: Converting to a Date

itshere — Wed, 22 Jun 2016 15:41:22 GMT

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

Re: Converting to a Date

majdi_ka — Wed, 22 Jun 2016 15:43:39 GMT

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

Re: Converting to a Date

itshere — Wed, 22 Jun 2016 15:48:34 GMT

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

Re: Converting to a Date

DeepakSwain — Wed, 22 Jun 2016 15:48:52 GMT

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;