topic Re: Concat Date and Number Value in SAS Programming

Concat Date and Number Value

ertweety — Fri, 04 Aug 2017 17:21:19 GMT

I need to concatenate a SAS date and number to match a concatenated field in excel.

Example;

In excel, my date is 01/31/2017 and the number I want to concatentate with it is 100. When I concatenate these 2 fields, I get 42766100.

How would I concatenate the same 2 values in SAS to return 42766100? The date is datetime in sas but I don't need the time part.

I have tried all the cat, catx, catq, cats functions to no success. I also tried adding 21916 to my date field but that didn't work either. I'm stuck. 😞

Re: Concat Date and Number Value

andreas_lds — Fri, 04 Aug 2017 15:30:53 GMT

data work.narf;
   a = '31Jan2017'd;
   b = 100;
   c = cat(a+21916 ,b);

   put _all_;
run;

Edit: The log says: a=20850 b=100 c=42766100 _ERROR_=0 _N_=1

Re: Concat Date and Number Value

art297 — Fri, 04 Aug 2017 15:38:11 GMT

If you want it to be a number, just expand on @andreas_lds's suggestion:

data have;
  input date mmddyy10.;
  cards;
01/31/2017
;

data want;
  set have;
  newdate=input(catt(date+21916,'100'),best32.);
run;

Art, CEO, AnalystFinder.com

Re: Concat Date and Number Value

AlanC — Fri, 04 Aug 2017 16:49:50 GMT

You are not asking this directly but I am doing some inferring (if I missed it, just ignore).

When you deal with datetime values in Excel or SAS, you have to work with the epochal dates to do the conversions correctly.

For example, Excel has an epochal date of: Jan 1, 1900 unless it is coming from a Mac (it uses 1904). SAS uses Jan 1, 1960. That is the number of ticks/seconds/days since that date. SAS has NO concept of a datetime. it merely uses a double for everything and interprets on the fly.

So, bottom-line is that if you are trying to do datetime conversions here, ping us back and ask. That is much more complex than doing the concatenation.

In your example, you are getting the number of days since 1960, not the date, converted into a SAS double. It is better to simply multiply the value times 100 and it achieves the same thing w/o the concatenation cost.

Re: Concat Date and Number Value

ertweety — Fri, 04 Aug 2017 17:01:40 GMT

Thanks for the help.

I can't seem to get it to work with either suggestion. I tried different date formats...didn't work. best32. is not recognized as well.

My data looks as follows;

dt = 31JAN2017:00:00:00

xfer_rsn_cd = 100

rsubmit;
data xfer_cds;
set xfer_cd;
cat=catt(dt+21916,xfer_rsn_cd);
run;

My output looks like

1801461916100

Re: Concat Date and Number Value

ballardw — Fri, 04 Aug 2017 17:17:32 GMT

@ertweety wrote:

Thanks for the help.

I can't seem to get it to work with either suggestion. I tried different date formats...didn't work. best32. is not recognized as well.

My data looks as follows;

dt = 31JAN2017:00:00:00

xfer_rsn_cd = 100

My output looks like

1801461916100

Your dt is a datetime value, not a date and when you deal with datetimes the internal representation in SAS is Seconds. You said Date, not time, which in SAS is days from an offset.

So you should show EXACTLY what you want to expose to Excel, whether it is numeric or string value, and the starting SAS value.

One of my pet peeves is calling a datetime value a date. They are WAY different in any data storage system I am familiar with and you get errors applying DATE functions to DATETIMES and vice versa.

Re: Concat Date and Number Value

ertweety — Fri, 04 Aug 2017 17:19:51 GMT

Sorry.

You are correct. It is datetime value. However, i don't need the time portion.

Re: Concat Date and Number Value

art297 — Fri, 04 Aug 2017 17:36:40 GMT

data have;
  input date anydtdtm21.;
  cards;
01/31/2017:00:00:00
;

data want;
  set have;
  newdate=input(catt(datepart(date)+21916,100),best32.);
run;

Art, CEO, AnalystFinder.com

Re: Concat Date and Number Value

ertweety — Fri, 04 Aug 2017 17:44:32 GMT

Thank you so much for the help!

Re: Concat Date and Number Value

AlanC — Fri, 04 Aug 2017 17:56:13 GMT

Sub newdate in the above with this:

newdate=(date/86400) || '100';

86400 is the total number of seconds in a day.