BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ANKH1
Pyrite | Level 9

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. 

IDDSmonthstime_pointvar3
1DS123.
1DS133.
1DS13.53.
1DS11.53.
1DS166.
1DS17.56.
1DS233.
1DS266.
1DS3 .1
1DS4 .0
2DS11..
2DS13.43 
2DS17.59 
2DS199.
2DS21 .
2DS23.43.
2DS299.
2DS3..2
2DS4..3
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

@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...

--
Paige Miller

View solution in original post

18 REPLIES 18
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
ANKH1
Pyrite | Level 9

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.

Patrick
Opal | Level 21

"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. 

Kurt_Bremser
Super User

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).

ANKH1
Pyrite | Level 9
Thanks for sharing the links!
PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
ANKH1
Pyrite | Level 9

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!

PaigeMiller
Diamond | Level 26
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.

  

--
Paige Miller
ANKH1
Pyrite | Level 9

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;
sbxkoenk
SAS Super FREQ

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

ANKH1
Pyrite | Level 9
I see! Thanks for the explanation.
PaigeMiller
Diamond | Level 26

@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...

--
Paige Miller
Tom
Super User Tom
Super User

@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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 18 replies
  • 3271 views
  • 10 likes
  • 8 in conversation