topic Re: Replace "." with blanks in a dataset in SAS Programming

Replace "." with blanks in a dataset

ANKH1 — Thu, 12 Oct 2023 22:05:10 GMT

Hi, this might be very simple but I cannot find a simple answer online. Perhaps I am asking it the wrong way. We have this dataset, but we need to uniformly either replace the "." with blanks or all blanks with "." This dataset is the result after combining multiple datasets.

ID	DS	months	time_point	var3
1	DS1	2	3	.
1	DS1	3	3	.
1	DS1	3.5	3	.
1	DS1	1.5	3	.
1	DS1	6	6	.
1	DS1	7.5	6	.
1	DS2	3	3	.
1	DS2	6	6	.
1	DS3		.	1
1	DS4		.	0
2	DS1	1	.	.
2	DS1	3.4	3
2	DS1	7.5	9
2	DS1	9	9	.
2	DS2	1		.
2	DS2	3.4	3	.
2	DS2	9	9	.
2	DS3	.	.	2
2	DS4	.	.	3

Re: Replace "." with blanks in a dataset

PaigeMiller — Thu, 12 Oct 2023 22:07:31 GMT

Please tell us if these variables are numeric or character, according to SAS (using PROC CONTENTS or using the SAS data set viewer)

Re: Replace "." with blanks in a dataset

Reeza — Thu, 12 Oct 2023 22:08:55 GMT

. represent numeric missing. If you want blanks when you export the data to a different format use option missing='';

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lesysoptsref/n0qamf3yfjtwzhn1ran6xwblnlea.htm#:~:text=Specifies%20the%20character%20to%20print%20for%20missing%20numeric%20values.&text=Default%3A,is%20a%20period%20(.).

Re: Replace "." with blanks in a dataset

sbxkoenk — Thu, 12 Oct 2023 22:11:59 GMT

A missing value for a CHAR variable is a blank.
A missing value for a NUMERIC variable is a dot ( . ).

Do you really want to change that "behavior" of SAS??
It's possible, but a bit of a weird idea and I wouldn't do it.

If you convert your numeric variable into a character one, the dots become blanks.

Koen

Re: Replace "." with blanks in a dataset

ANKH1 — Thu, 12 Oct 2023 22:30:56 GMT

They're characters. Before merging the datasets, I had to change numeric or boolean to character variables to allow to display the answers as Yes/No in the final table. The odd thing, or maybe is not?, is that for a character variable that was not changed, when merging, this same variable has some "." and others are blank.

Re: Replace "." with blanks in a dataset

Patrick — Fri, 13 Oct 2023 04:30:22 GMT

"Before merging the datasets, I had to change numeric or boolean to character variables to allow to display the answers as Yes/No in the final table. "

Then make sure that when you convert the values from char to num that you don't get a period for missing numerical values.

Also if this is just about printing numerical values then consider to create a format for the original variable instead of creating an additional character variable.

Re: Replace "." with blanks in a dataset

Astounding — Fri, 13 Oct 2023 06:40:24 GMT

It might take 3 simple statements, such as:

if var3="." then var3=" ";

However, first confirm the values that look like blanks or dots. Check using:

proc freq data=have;
tables months timepoint var3 ;
format months timepoint var3 $hex6.;
run;

Re: Replace "." with blanks in a dataset

Kurt_Bremser — Fri, 13 Oct 2023 06:53:59 GMT

Don't convert values like these to character. In character strings, 10 will be sorted before 2, just to name one issue. Boolean values are also best stored as numbers, with a format which displays 0 as "no", 1 as "yes" and missing as blank or "N/A". This allows direct use in conditions (0 and missing are considered false).

Re: Replace "." with blanks in a dataset

PaigeMiller — Fri, 13 Oct 2023 08:14:37 GMT

@ANKH1 wrote:

The odd thing, or maybe is not?, is that for a character variable that was not changed, when merging, this same variable has some "." and others are blank.

As I said before, you need to show us your code. We don't know what you did, and if we're going to explain this, we need to see your code.

But I agree with the others, you should be using formats and not character variables to represent numeric values. And I agree with @sbxkoenk it doesn't seem like a good idea to change the way SAS handles missing values. You could write your own code to do so, of course (which seems like what you are trying to do) but why bother, what is the benefit, it is extra work that doesn't seem to provide value and that combination of "Extra Work" with "No Value" is something I advise you to avoid.

Re: Replace "." with blanks in a dataset

ANKH1 — Fri, 13 Oct 2023 20:39:29 GMT

Thank you for everyone's answers. I am learning best practices in SAS so this is very helpful. I am not sure how to use formats. Is this within the same data step or a previous data step?This is the code I used to convert the variable type.

data ds1;
set ds;
character_var1= put(var3, 8.); /*Categories*/
character_var2= put(var2, 8.); /*Boolean*/
if character_var1 = 1 then character_var1 = 'Yes';
else if character_var1 = 2 then character_var1 = 'No';
else if character_var1 = 3 then character_var1 = 'Unknown';

if if character_var2 = 1 then character_var2 = 'Yes';

run;

Here you can a sample where two variables that are numeric, are converted to character so we can print out the actual answer.

Thanks!

Re: Replace "." with blanks in a dataset

PaigeMiller — Fri, 13 Oct 2023 20:46:46 GMT

proc format;
    value ynu 1='Yes' 2='No' 3='Unknown';
    value bool 1='Yes';
run;
data ds1;
    set ds;
    format var3 ynu. var2 bool.;
run;
proc print data=ds1;
run;

With formats, you don't need to create character variables, and in fact you don't even need a new data set. When you created DS, you could assign the format then, and if you did that, ds1 would not need to be created at all.

Re: Replace "." with blanks in a dataset

ANKH1 — Fri, 13 Oct 2023 20:48:50 GMT

Great! I think we want to keep ds as it is. But if we were to format directly on ds it should look like this?

proc format;
    value ynu 1='Yes' 2='No' 3='Unknown';
    value bool 1='Yes';
run;

data ds;
    format var3 ynu. var2 bool.;
run;
proc print data=ds;
run;

Re: Replace "." with blanks in a dataset

sbxkoenk — Fri, 13 Oct 2023 21:38:15 GMT

Hello,

Even if you don't make a new data set ds1 , you need set statement :

data ds;
  set ds;
  format var3 ynu. var2 bool.;
run;

If your data set is super big (big size), I would re-format the columns with PROC DATASETS instead.
With PROC DATASETS you can change metadata only (without processing every observation in the data set -- with the data step above you input and output every observation without doing anything with it -- that's a waste of time and resources).

BR, Koen

Re: Replace "." with blanks in a dataset

Kurt_Bremser — Sat, 14 Oct 2023 05:42:12 GMT

proc datasets lib=work;
modify ds;
format var3 ynu. var2 bool.;
quit;

Re: Replace "." with blanks in a dataset

PaigeMiller — Sat, 14 Oct 2023 10:21:46 GMT

@ANKH1 wrote:

Great! I think we want to keep ds as it is. But if we were to format directly on ds it should look like this?
proc format;
    value ynu 1='Yes' 2='No' 3='Unknown';
    value bool 1='Yes';
run;

data ds;
    format var3 ynu. var2 bool.;
run;
proc print data=ds;
run;

You would need a SET statement in the DATA step like this:

data ds;
    set ds;
    format var3 ynu. var2 bool.;
run;

But adding formats should be done in a data step only when you CREATE the data set. On a data set that already exists, please use the code from @Kurt_Bremser at https://communities.sas.com/t5/SAS-Programming/Replace-quot-quot-with-blanks-in-a-dataset/m-p/898586#M355177

Re: Replace "." with blanks in a dataset

Tom — Sat, 14 Oct 2023 15:48:42 GMT

@ANKH1 wrote:

Thank you for everyone's answers. I am learning best practices in SAS so this is very helpful. I am not sure how to use formats. Is this within the same data step or a previous data step?This is the code I used to convert the variable type.
data ds1;
set ds;
character_var1= put(var3, 8.); /*Categories*/
character_var2= put(var2, 8.); /*Boolean*/
if character_var1 = 1 then character_var1 = 'Yes';
else if character_var1 = 2 then character_var1 = 'No';
else if character_var1 = 3 then character_var1 = 'Unknown';

if if character_var2 = 1 then character_var2 = 'Yes';

run;
Here you can a sample where two variables that are numeric, are converted to character so we can print out the actual answer.

Thanks!

So you set CHARACTER_VAR1 to the string ' 1' (seven spaces followed by the digit 1) and then later you ask SAS to convert it back into a number so you can compare it to the number 1 so you can decide to convert it to the string 'YES' instead?

Why not just put 'YES" into it do begin with?

data ds1;
  set ds;
  length character_var1 $7 ;
  if var3 = 1 then character_var1 = 'Yes';
  else if var3 = 2 then character_var1 = 'No';
  else if var3 = 3 then character_var1 = 'Unknown';
run;

Re: Replace "." with blanks in a dataset

ANKH1 — Mon, 16 Oct 2023 14:33:30 GMT

Thanks for sharing the links!

Re: Replace "." with blanks in a dataset

ANKH1 — Mon, 16 Oct 2023 14:37:22 GMT

I see! Thanks for the explanation.

Re: Replace "." with blanks in a dataset

ANKH1 — Mon, 16 Oct 2023 14:41:20 GMT

That was my first try, but the log marked an error, something along the lines of the variable not having the right format.