DATA Step, Macro, Functions and more

Concat Date and Number Value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Concat Date and Number Value

[ Edited ]

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. Smiley Sad


Accepted Solutions
Solution
3 weeks ago
PROC Star
Posts: 7,364

Re: Concat Date and Number Value

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

 

 

View solution in original post


All Replies
Super Contributor
Posts: 264

Re: Concat Date and Number Value

[ Edited ]
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

PROC Star
Posts: 7,364

Re: Concat Date and Number Value

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

 

Occasional Contributor
Posts: 15

Re: Concat Date and Number Value

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

Super User
Posts: 10,536

Re: Concat Date and Number Value


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.

Occasional Contributor
Posts: 15

Re: Concat Date and Number Value

Sorry.

 

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

Contributor
Posts: 62

Re: Concat Date and Number Value

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. 

 

  

Solution
3 weeks ago
PROC Star
Posts: 7,364

Re: Concat Date and Number Value

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

 

 

Occasional Contributor
Posts: 15

Re: Concat Date and Number Value

Thank you so much for the help!

Contributor
Posts: 62

Re: Concat Date and Number Value

Sub newdate in the above with this:

 

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

 

86400 is the total number of seconds in a day. 

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 196 views
  • 2 likes
  • 5 in conversation