topic Re: how remove empty values in SAS Programming

how remove empty values

Satora_In — Tue, 31 Oct 2023 12:14:22 GMT

Dear friends,

I wrote a request only it has a problem that the number of columns is not static but dynamic. Because of this, in my guide they are displayed as a dot and for the catx functions, I have a problem that empty values appear as a dot and a comma (.;.;) in this format. How can I remove empty values to avoid this problem? Thanks for the help.
I have attached my request. Perhaps it can be solved through the condition, help.

data mytable;
set WORK.QUERY_FOR_TRNSTRANSPOSED;
length result $4100;
result = catx(';',Столбец1,Столбец2,Столбец3,Столбец4,Столбец5,Столбец6,Столбец7,Столбец8,Столбец9,Столбец10,Столбец11,Столбец12,Столбец13,Столбец14,Столбец15,
Столбец16,Столбец17,Столбец18,Столбец19,Столбец20,Столбец21,Столбец22,Столбец23,Столбец24,Столбец25,Столбец26,Столбец27,Столбец28,Столбец29,Столбец30,Столбец31,Столбец32,
Столбец33,Столбец34,Столбец35,Столбец36,Столбец37,Столбец38,Столбец39,Столбец40);
format result $char.;
run;

Re: how remove empty values

JosvanderVelden — Tue, 31 Oct 2023 12:50:15 GMT

You can try with something similar to the code below:

data have;
   input var1 var2 var3 var4 var5;
datalines;
1 . 3 . 5
;
run;

data want(keep=result);
   set have;
   length result $50;
   result = transtrN(catx(';', of var:), ".;", trimn(' '));
   put result=;
run;

Re: how remove empty values

Tom — Tue, 31 Oct 2023 13:40:28 GMT

Are the variables NUMERIC?

If so change the MISSING option before running the CAT... function.

options missing=' ';
data mytable;
  set WORK.QUERY_FOR_TRNSTRANSPOSED;
  length result $4100;
  result = catx(';', of Столбец1-Столбец40);
run;

PS: Do NOT attach a FORMAT to a plain text variable.

Re: how remove empty values

Amir — Tue, 31 Oct 2023 14:43:41 GMT

Hi,

Please clarify:

1) If you have an observation with data like:

a;b;c;.;d;e

Do you want the result to have 2 semicolons (";") next to each other, with or without a space, like:

a;b;c;;d;e

Or do you want to remove the semicolon too, like:

a;b;c;d;e

2) If the whole observation looks like:

.;.;.;.;.

Then do you want to output that observation, if yes, then what should the result look like?

If my questions are not clear, then please provide some example data in the form of a data step, showing input data in one data step and required output in another data step.

Thanks & kind regards,

Amir.

Re: how remove empty values

ballardw — Tue, 31 Oct 2023 14:45:17 GMT

One approach is to set OPTIONS MISSING=' '; before the data step. That way the conversion of missing values (not empty) will be to a space character. The Catx function will trim leading and trailing spaces before concatenation, so there is no period or space in the result for the missing values.

options missing=' ';
data mytable;
set WORK.QUERY_FOR_TRNSTRANSPOSED;
length result $4100;
result = catx(';',Столбец1,Столбец2,Столбец3,Столбец4,Столбец5,Столбец6,Столбец7,Столбец8,Столбец9,Столбец10,Столбец11,Столбец12,Столбец13,Столбец14,Столбец15,
Столбец16,Столбец17,Столбец18,Столбец19,Столбец20,Столбец21,Столбец22,Столбец23,Столбец24,Столбец25,Столбец26,Столбец27,Столбец28,Столбец29,Столбец30,Столбец31,Столбец32,
Столбец33,Столбец34,Столбец35,Столбец36,Столбец37,Столбец38,Столбец39,Столбец40);
format result $char.;
run;

/* reset to the default */
options missing='.';