Concatenate Columns

[anweinbe]

Hi All,

I have a SAS table with 8 columns, the last 2 of which are Permno and Eventdat. I am looking to add a 9th column that takes the data within the Permno Column and concatenates it with the data in the Eventdat Column with a "-" in between. I found another post on the topic that was older but it didn't appear to work for me. Here is the code I currently have. If possible I would also like to strip out any leading and trailing blanks. 

I've also tried putting "permno" and "eventdat" in quotes but then it just shows up as words. My other variables disappear too!

data temp.biotechControlsFinal;
var1 = Permno;
var2 = Eventdat;
length var3 $ 15;
var3 = catx('-', var1, var2);
put _all_;
run;

[Reeza]

You need to format your dates, but it should work otherwise. 

var3 = catx('-', put(var1, date11.), put(var2, date11.));

If that doesn't work please show the log and indicate what is occurring, in detail. "Not working" is vague and doesn't provide very much information on what is or is not working.

---

@anweinbe wrote:

Hi All,

I have a SAS table with 8 columns, the last 2 of which are Permno and Eventdat. I am looking to add a 9th column that takes the data within the Permno Column and concatenates it with the data in the Eventdat Column with a "-" in between. I found another post on the topic that was older but it didn't appear to work for me. Here is the code I currently have. If possible I would also like to strip out any leading and trailing blanks. 

I've also tried putting "permno" and "eventdat" in quotes but then it just shows up as words. My other variables disappear too!

data temp.biotechControlsFinal;
var1 = Permno;
var2 = Eventdat;
length var3 $ 15;
var3 = catx('-', var1, var2);
put _all_;
run;

---

[Tom · Accepted solution]

You need to tell the data step where to find the original data.  Also use your variables names. Make sure to make the new variable, VAR3 in code below, long enough.  You probably don't want to overwrite your original dataset, so make a new one.

data want ;
  set temp.biotechControlsFinal;
  length var3 $ 15;
  var3 = catx('-', Permno, Eventdat);
run;

Also if EVENTDAT is a date you might need to add a PUT() function to convert it to the string you want to use in the new variable instead of the raw number of days since 1960 that SAS uses to store dates.

data want ;
  set temp.biotechControlsFinal;
  length var3 $ 30;
  var3 = catx('-', Permno, put(Eventdat,yymmdd10.));
run;

[anweinbe]

Tom,

The Eventdat is currently a Date9. An example of my data is 15FEB2017. Is there a way to keep it just as is? 

So if my Permno is 12345 and the date is 15FEB2017, the result should will be 12345-15FEB2017

I tried both of what you sent but it converts the date. You noted that above. Is there an easy way to fix that?

[Tom]

You can use any format you want in the PUT() statement. You can also use the VVALUE() function instead of the PUT() function to get the formatted value without having to know what format has been attached to the variable.
Personally I would not use that style represent a data value when including it in a string. It will not sort in chronological order. '10DEC2018' will appear before '20JAN2018'. Using date values in Year, Month, Day order means that lexical sorting is the same as chronological sorting.

[anweinbe]

Tom, I got it to work with the VVALUE() you suggested.

Do I need to worry about the sort if its turning it into a text and the Permno which just a 5 digit code is the beginning segment?

[Tom]

The date part will only play any role in the sorting (if you even ever need to sort) would be when there are multiple dates for the same value of the prefixed variable.