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

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChanceTGardener
SAS Employee
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

7 REPLIES 7
ybolduc
Quartz | Level 8

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

Aleixo
Quartz | Level 8

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

ybolduc
Quartz | Level 8

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;
Aleixo
Quartz | Level 8

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

ybolduc
Quartz | Level 8

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,

ChanceTGardener
SAS Employee
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;
novinosrin
Tourmaline | Level 20
proc sql;
create table want as 
select date,input(compress(date,"M"),yymmn6.) format=yymm7. as calculation
from have;
quit;

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
  • 7 replies
  • 1346 views
  • 1 like
  • 4 in conversation