BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ertweety
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21
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

9 REPLIES 9
andreas_lds
Jade | Level 19
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
Opal | Level 21

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
Obsidian | Level 7

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
Super User

@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
Obsidian | Level 7

Sorry.

 

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

AlanC
Barite | Level 11

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. 

 

  

https://github.com/savian-net
art297
Opal | Level 21
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
Obsidian | Level 7

Thank you so much for the help!

AlanC
Barite | Level 11

Sub newdate in the above with this:

 

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

 

86400 is the total number of seconds in a day. 

https://github.com/savian-net

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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