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

Hi Guys,

I currently have 2 different formats of the same data. (Basically they are just dates YYYYMM to me).

1 of them is in the format " LENGTH 6, FORMAT $6, , INFORMAT $6."

The other is in the format "LENGTH 8, FORMAT BEST12. , INFORMAT 12."

I tried to merge my data based on dates, but the log said that they have to be in the same format first.

How do I convert them into the same format? And Which is a better format to use?

Thanks a lot guys!

1 ACCEPTED SOLUTION

Accepted Solutions
ArtC
Rhodochrosite | Level 12

As Art suggested the first step is to convert all dates to SAS dates.  You do not need to do so here, but it is almost always a best practice.  Once dates are converted they can be used to merge or join the tables.  The formats currently assigned have nothing to do with the problem it is the stored values that you need to deal with.  Conside the following simple example of conversion of both numeric and character dates.

data char;

input cdate $6.;

date = mdy(input(substr(cdate,5,2),2.),01,input(substr(cdate,1,4),4.));

datalines;

199101

199202

run;

data num;

input ndate 6.;

date = mdy(mod(ndate,100),01,int(ndate/100));

datalines;

199202

199303

run;

data want;

   merge char num;

   by date;

   run;

proc print data=want;

run;

If you want the SAS date to appear in the YYYYMM format use the YYMMN6. format.

View solution in original post

4 REPLIES 4
art297
Opal | Level 21

I would create two new fields that contain SAS dates.  However, before going any further, does the one with length 8 actually only contain yyyymm or does it also contain the day of the month?

Benn
Calcite | Level 5

Hi Arthur,

It only contains YYYYMM.

ArtC
Rhodochrosite | Level 12

As Art suggested the first step is to convert all dates to SAS dates.  You do not need to do so here, but it is almost always a best practice.  Once dates are converted they can be used to merge or join the tables.  The formats currently assigned have nothing to do with the problem it is the stored values that you need to deal with.  Conside the following simple example of conversion of both numeric and character dates.

data char;

input cdate $6.;

date = mdy(input(substr(cdate,5,2),2.),01,input(substr(cdate,1,4),4.));

datalines;

199101

199202

run;

data num;

input ndate 6.;

date = mdy(mod(ndate,100),01,int(ndate/100));

datalines;

199202

199303

run;

data want;

   merge char num;

   by date;

   run;

proc print data=want;

run;

If you want the SAS date to appear in the YYYYMM format use the YYMMN6. format.

DanielSantos
Barite | Level 11

Hi.

There's two good reasons for keeping date values in numeric SAS date vars, being calculation and representation.

SAS Dates are required for the use of date functions such as INTNX and DATEDIFF and are the only valid values that are one "format away" from displaying a date in any way you wish.

That said, you could actually perform the join without converting one of the key columns, if and only if, your merge could translate to a SQL join.

Since SQL allow you to match by transformed columns it would just be a matter of finding the right match expression, maybe something like this:

...

on cats(put(SASDATE,year4.),put(SASDATE,month2.)) = cats(CHARDATE)

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 2136 views
  • 3 likes
  • 4 in conversation