DATA Step, Macro, Functions and more

Char to Dates format problems

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Char to Dates format problems

 

Hi,

 

This is my table with a format char, and i want to change format to YYMM7.  

 

I try this with no result

 

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_DATES AS
SELECT /* Calculation */
(input(t1.ANO_MES, anydate.)) FORMAT=YYMM7. AS Calculation
FROM WORK.DATES t1;
QUIT;

 

      .
2016M01
2016M02
2016M03
2016M04
2016M05
2016M06
2016M07
2016M08
2016M09
2016M10
2016M11
2016M12
2017M01
2017M02
2017M03
2017M04
2017M05
2017M06
2017M07
2017M08
2017M09
2017M10
2017M11
2017M12
2018M01
2018M02
2018M03
2018M04
2018M05
2018M06
2018M07

The output is the same to the input but in format of a date YYMM7.

 

It is very dificult to work with dates. I had to pass to char because i want to filter duplicates. I had 2 times: 

2018M07

 

but with a sort and keep only first observations SAS do not filter duplicates because this date have the day "hidden". That why am i want to change format to initial, YYMM7.

 

Regards,

Aleixo


Accepted Solutions
Solution
2 weeks ago
SAS Employee
Posts: 7

Re: Char to Dates format problems

data have;
input date $;
datalines;
2016M01
2016M02
2016M03
2016M04
2016M05
2016M06
2016M07
2016M08
2016M09
2016M10
2016M11
2016M12
2017M01
2017M02
2017M03
2017M04
2017M05
2017M06
2017M07
2017M08
2017M09
2017M10
2017M11
2017M12
2018M01
2018M02
2018M03
2018M04
2018M05
2018M06
2018M07
;
run;

proc sql;
create table work.want as 
select 	date
,	input(strip(scan(date,2,'M'))||'/01/'||strip(scan(date,1,'M')),mmddyy10.) format=yymm7. as calculation
from work.have;
quit;

View solution in original post


All Replies
Contributor
Posts: 25

Re: Char to Dates format problems

If you start with the date as a date, why don't you simply use the INTNX function to remove the "day" part, that would be much easier that trying to do format/informat gymnastic...

Contributor
Posts: 69

Re: Char to Dates format problems

Thanks your answer.

 

That is only for a year, or for a month, or for a day.

 

I can not extract simultanious year and month. Only if i use a catx or cats but it is a char, not a date

 

Regards,
Aleixo

Contributor
Posts: 25

Re: Char to Dates format problems

Maybe I misunderstand what you're trying to do...

 

The following code is a very straightforward way of "stripping" the DAY of in a date. After that, you can run proc sort nodupkey to remove duplicates if you need.

 

It effectively "extracts" the Year and Month part if you want.

 

With larger datasets, going back and forth to a character string is not very efficient...

 

data dates;
  input date ANYDTDTE.;
  format date date2 DATE.;
  date2 = intnx('month', date, 0, 'b');
datalines;
2018MAY01
2018MAY02
;
run;
Contributor
Posts: 69

Re: Char to Dates format problems

In practice i don't have the day. I want to remove duplicates os YearMMonth. The problem is when i do a proc sort nodupkey, SAS not remove duplicate because my collumn YearMMonth has the day "hidden". 

 

It was very strange what happen. I do not understans why SAS assume the day in a collumn with format YYMM7. That is my question.

 

I am not able to answer for that question. That's why i had to do this gymnastic, date -> char (to remove duplicates) -> date (again)

 

Regards,
Aleixo

Contributor
Posts: 25

Re: Char to Dates format problems

Hi Alexio,

 

That is why I'm recommending the use of INTNX, your ensure that the "day" part of each date is set to 1 (beginning of the month). This way, when you do the PROC SORT NODUPKEY, all dates in a given month will be dedupped.

 

With INTNX you should be able to perform that vast majority of your date transformations.

 

The format you apply on a colum does not change the content. Thus, even if you took a date column and applied a format that does not display the day, the actual data still contains the information about the day. This is where the use if INTNX is important.

 

Thanks,

Solution
2 weeks ago
SAS Employee
Posts: 7

Re: Char to Dates format problems

data have;
input date $;
datalines;
2016M01
2016M02
2016M03
2016M04
2016M05
2016M06
2016M07
2016M08
2016M09
2016M10
2016M11
2016M12
2017M01
2017M02
2017M03
2017M04
2017M05
2017M06
2017M07
2017M08
2017M09
2017M10
2017M11
2017M12
2018M01
2018M02
2018M03
2018M04
2018M05
2018M06
2018M07
;
run;

proc sql;
create table work.want as 
select 	date
,	input(strip(scan(date,2,'M'))||'/01/'||strip(scan(date,1,'M')),mmddyy10.) format=yymm7. as calculation
from work.have;
quit;
PROC Star
Posts: 1,836

Re: Char to Dates format problems

proc sql;
create table want as 
select date,input(compress(date,"M"),yymmn6.) format=yymm7. as calculation
from have;
quit;
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 94 views
  • 1 like
  • 4 in conversation