topic Re: concatenate with adding character for missing values in SAS Programming

concatenate with adding character for missing values

noda6003 — Thu, 07 Jan 2021 16:44:09 GMT

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?

Re: concatenate with adding character for missing values

novinosrin — Thu, 07 Jan 2021 17:00:48 GMT

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

Re: concatenate with adding character for missing values

me55 — Thu, 07 Jan 2021 17:02:30 GMT

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.

Re: concatenate with adding character for missing values

Amir — Thu, 07 Jan 2021 17:25:56 GMT

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.

Re: concatenate with adding character for missing values

ballardw — Thu, 07 Jan 2021 17:33:55 GMT

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.

Re: concatenate with adding character for missing values

noda6003 — Thu, 07 Jan 2021 17:59:35 GMT

have

col1 col2 col3

1 2 .

1 3 2

2 1 3

want

col4

1/2/Y

1/3/2

2/1/3

Re: concatenate with adding character for missing values

noda6003 — Thu, 07 Jan 2021 18:06:24 GMT

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

But it does not work

Re: concatenate with adding character for missing values

Tom — Thu, 07 Jan 2021 18:10:29 GMT

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

Re: concatenate with adding character for missing values

novinosrin — Thu, 07 Jan 2021 18:12:59 GMT

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;