Concat Date and Number Value

[ertweety]

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

[andreas_lds]

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

[art297]

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

[ertweety]

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

[ballardw]

---

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

[ertweety]

Sorry.

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

[AlanC]

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. 

[art297 · Accepted solution]

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

[ertweety]

Thank you so much for the help!

[AlanC]

Sub newdate in the above with this:

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

86400 is the total number of seconds in a day.