DATA Step, Macro, Functions and more

Converting to a Date

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Converting to a Date

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. 

 


Accepted Solutions
Solution
‎06-28-2016 11:58 AM
Contributor
Posts: 41

Re: Converting to a Date

 

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;

 

 

 

View solution in original post


All Replies
Contributor
Posts: 41

Re: Converting to a Date

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 !

 

Contributor
Posts: 26

Re: Converting to a Date

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

Solution
‎06-28-2016 11:58 AM
Contributor
Posts: 41

Re: Converting to a Date

 

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;

 

 

 

Contributor
Posts: 26

Re: Converting to a Date

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?

Contributor
Posts: 41

Re: Converting to a Date

Can you please share your code ?
Contributor
Posts: 26

Re: Converting to a Date

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)Smiley SadColumn).
1 at 17:15
Contributor
Posts: 41

Re: Converting to a Date

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 ?
Contributor
Posts: 26

Re: Converting to a Date

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

Re: Converting to a Date

you forgot the set statement with the table containing your variable start_time.
Contributor
Posts: 26

Re: Converting to a Date

but will it create a new column or overwrite the existing column?
Frequent Contributor
Posts: 104

Re: Converting to a Date

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;

 

Swain
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 460 views
  • 2 likes
  • 3 in conversation