Help using Base SAS procedures

Formatting Variables

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Formatting Variables

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!


Accepted Solutions
Solution
‎10-07-2012 02:14 AM
Valued Guide
Posts: 632

Re: Formatting Variables

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


All Replies
PROC Star
Posts: 7,356

Re: Formatting Variables

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?

Contributor
Posts: 35

Re: Formatting Variables

Hi Arthur,

It only contains YYYYMM.

Solution
‎10-07-2012 02:14 AM
Valued Guide
Posts: 632

Re: Formatting Variables

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.

Super Contributor
Posts: 474

Re: Formatting Variables

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

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 508 views
  • 3 likes
  • 4 in conversation