Desktop productivity for business analysts and programmers

Setting a missing value to a variable using create format

Reply
New Contributor
Posts: 3

Setting a missing value to a variable using create format

Hi,

 

I'm trying to set the values of a variable to MISSING ('') using the create format task.

 

the original table looks somewhat like

 

Name     Gender

Wally        U

Walter      M

Violeta     F

Viktor       M

Viktoria 

Victoria    F

Victoria

Tim          M

 

The Gender is classified as F for female, M for male, U or MISSING value for undefined.

I like to set all gender values of

'F' to 'female',

'M' to 'male' and

'U' or MISSING value to MISSING.

 

Afterwards the table should look like this

 

Name     Gender

Wally       

Walter      male

Violeta     female

Viktor       male

Viktoria 

Victoria    female

Victoria

Tim          male

 

I tried to do so by using the create format task but if I leave the label for "all other values" empty I get two single quotaion marks ('') instead of empty entries. My table looks like

 

 

Name     Gender

Wally        ''

Walter      male

Violeta     female

Viktor       male

Viktoria    ''

Victoria    female

Victoria    ''

Tim          male

 

I wonder if it is possible to do this with the create format task or do ii have to use a CASE-WHEN-THEN-ELSE statement?

 


code format gender.PNGUI create format gender.PNG
Super User
Super User
Posts: 7,725

Re: Setting a missing value to a variable using create format

Sorry, I dont know what a "format task" is, not that familiar with EG anymore.  However it is a very simple task in code:

proc format;
  value $gender
    "M"="male"
    "F"="female"
    other=" ";
run;

data want;
  set have;
  format gender $gender.;
run;
New Contributor
Posts: 3

Re: Setting a missing value to a variable using create format

[ Edited ]

Hi RW9

 

thanks for your answer.

I myself came from SAS Base and I know that it is easy to do in code.

 

What makes me wonder is that the EPG sets exactly the code you produced (see attached picture in orig post) but gives me two simple quotation marks instead of an empty entry.

Trusted Advisor
Posts: 1,137

Re: Setting a missing value to a variable using create format

Please select the all other values from the drop down where all other values is highlighted that way for other values we get the blank

 

Capture1.JPG

 

Thanks,
Jag
New Contributor
Posts: 3

Re: Setting a missing value to a variable using create format

Hi Jag,

 

I tried it the same way, but what happend was that for all other values I got two single quotation marks.

 

If I use a format created like you did (named $Gender.) I get the results as shown in the provided picture.

 

Code for column Gender_1:

CASE
WHEN t1.Gender_orig = 'W' THEN 'Weibl.'
WHEN t1.Gender_orig = 'M' THEN 'Männl.'
ELSE ''
END

 

Code for Gender_2:

PUT(t1.Gender_orig, $Gender.)

 

Code for Gender_3:

use format $Gender. as output format

 


gender example.PNG
Ask a Question
Discussion stats
  • 4 replies
  • 115 views
  • 0 likes
  • 3 in conversation