topic Re: Formatting Variables in SAS Procedures

Formatting Variables

Benn — Sun, 07 Oct 2012 03:48:38 GMT

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!

Re: Formatting Variables

art297 — Sun, 07 Oct 2012 04:14:36 GMT

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?

Re: Formatting Variables

Benn — Sun, 07 Oct 2012 04:44:02 GMT

Hi Arthur,

It only contains YYYYMM.

Re: Formatting Variables

ArtC — Sun, 07 Oct 2012 06:14:18 GMT

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.

Re: Formatting Variables

DanielSantos — Mon, 08 Oct 2012 08:21:35 GMT

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