concatenate with adding character for missing values

[noda6003]

I want to concatente 3 variables and if i have missing varaible at end than want to replace it with "Y".

Can anyone help me how to do it?

[novinosrin]

Hi @noda6003  Can you please post a sample of HAVE and WANT(expected output) explaining the logic?

[noda6003]

have

col1    col2       col3

1          2            .

1          3           2

2          1           3

want

col4

1/2/Y

1/3/2

2/1/3

[Tom]

The CAT... functions will honor the MISSING option setting.

data have ;
  input col1-col3 ;
cards;
1          2            .
1          3           2
2          1           3
;

options missing='Y';
data want;
  set have;
  want=catx('/', of col1-col3);
run;
options missing='.';

proc print;
run;

Obs    col1    col2    col3    want

 1       1       2       .     1/2/Y
 2       1       3       2     1/3/2
 3       2       1       3     2/1/3

[me55]

the easiest way to do what you are doing is to update the table to y where value=null. to concantenate, you would want to use the cat function of which there are many varieties like cat-s, cat-t...here is a webpage on using the cat function...

SAS CAT-S Function 

an example would be...

data want;

  data have;

  new_var=catx('',var1,var2,var3);

run;

that is the data step version.  i work more in proc sql.  i can give you an example of that if you want but that should work.  

[Amir]

Hi @noda6003 ,

As @novinosrin has requested, it really helps to see the data, including data types.

In principle, the following logic might be a good starting point:

/* set up data in "have" data set */
data have;
   infile datalines truncover;

   input
      var1 : $char1.
      var2 : $char1.
      var3 : $char1.
   ;

   datalines;
a b c
d e  
g h i
;

/* create data in "want" data set based on "have" data set */
data want;
   set have;
   length result $ 3;

   /* if var3 is missing then use "Y" otherwise use var3 */
   result = cats(var1,var2,ifc(missing(var3),'Y',var3));
run;

The processing in the 2nd data step should also work for numeric input variables.

If this is not what you want then please provide example data and logic as requested.

Kind regards,

Amir.

[ballardw]

Please show the code you are using to concatenate the variables.

I suspect you can replace one of the variables with : coalescec(varname,'Y')

The coalescec function returns the first non-missing value in a list of character values be they variables or literals.

Coalesce does the same thing for numeric values.

[noda6003]

I use  ct = cats("/",col1,col2,ifc(missing(col3),'Y',col3));

But it does not work

[novinosrin · Accepted solution]

good attempt @noda6003  just a tweak-

data have;
 input var1-var3;
 cards;
1          2            .

1          3           2

2          1           3
;

data want;
 set have;
 length want $10;
 want=catx('/',var1,var2,ifc(var3=.,'Y',put(var3,8.)));
run;