BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8


Hi,

I have a numeric variable which has values

1

2

.

i wrote the format

proc format;

value char

1="ONE"

2="TWO"  

<<<<<<<<<<<<<<<dint use anything for the .(missing value)

in the data step i used

Z=put(numeric variable,char.) so the values are converted to a charecter now

Now I am doing proc freq containing this variable and I dint want to get the missing(.)

So in the proc freq I use :

where (numeric variable) ne ".";

Even then the periood remains............

I had to go to the format and write other =" ";  to arrive at the result...Finally it gives Missing in the Contingency table at the bottom with out showing in the table itself...

Could somebody explain the logic behind????

Thanks a ton

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

I think you mixed the missing value of both numeric and character.

where (numeric variable) ne .  ;

OR

where not missing(numeric variable) ;

Ksharp

View solution in original post

13 REPLIES 13
Ksharp
Super User

I think you mixed the missing value of both numeric and character.

where (numeric variable) ne .  ;

OR

where not missing(numeric variable) ;

Ksharp

robertrao
Quartz | Level 8

Thanks for the reply,

In the datastep i used a put function to apply the format above.

The format gets applied and . (missing stays as it is)

But the variable attributes is char now so i tried to use

where (numeric variable) ne" ." ;

Still missing appears in the 2*2 table..

robertrao
Quartz | Level 8

Hi,

Another question is that

Can where clause be used with Numeric variable????What is in brackets is a numeric variable???can we use it???

where (numeric variable) ne .  ;

OR

where not missing(numeric variable) ;

Ofter times I get this Error: Where clause operator requires compatible variables!!!!

Thanks

ArtC
Rhodochrosite | Level 12

As Ksharp suggests I believe that you are confusing numeric and character missing values;

The numeric variable is not changed by the PUT function.  The new variable Z is character and the period (.) is no longer a missing value.

Is the following something like you are seeing (you did not show much code so I am guessing)?  Notice that the missing value for NUM is automatically excluded by FREQ (it is a classification variable), while Z has no missing values.

proc format;
value char
1="ONE"
2="TWO";

run;

data have;
input num;
z=put(num,char.);
datalines;
1
2
.
run;
proc freq data=have;
table num*z;
format num char.;
run;

robertrao
Quartz | Level 8

Hi ,

Thanks for the detailed explanation.

I did not do it the way you showed me.

But in the format i wrote like this:

proc format;
value char
1="ONE"
2="TWO";

other=" "                         <<<<<<<--------------------

;

run;

But it took me a long time to figure this out....and got the missing(.) eliminated from the contingency table

So the other way is how you showed it above???

Thanks

ballardw
Super User

Another way is before the data step using the put function set  Option missing=' '; The format approach is probably better most times but knowing about the option as an alternative is useful.

Also, remember to set mising back to '.' when done.

robertrao
Quartz | Level 8

Hi ,

I also dint understand how the missing will be set back to "." after done????

Could you please show an example of the put function set  Option missing=' ';

Regards

ballardw
Super User

The output for put statement or put function behaves the same with the option missing settings. Look in log for example 

data _null_;

x = .;

put 'Option missing=.' x;

run;

option missing=' '; /* set missing to blank*/

data _null_;

x = .;

put 'Option missing= blank' x;

run;

option missing='.'; /* reset to . when done */

robertrao
Quartz | Level 8

hiiii,

After I run thwe complete program I get a 2*2 table with

num varaible having values ONE TWO

z variable having values  .   ONE TWO

                       NUM                  Z

                                             .        ONE      TWO

                       ONE             0          1            0    

                       TWO             0           0           1

How can i remove the . from Z now????I do not want it to be seen in the table!!!!

I guess THAT would be to use"

proc format;

value char

1="ONE"

2="TWO"

. =" "

;

RUN;

Any other methods....?????????

ArtC
Rhodochrosite | Level 12

Assuming that you are using the CHAR. format shown in your post (the version mapping . to ' ') (numeric missing to character missing), you should be seeing just the columns ONE and TWO for both NUM and Z.  PROC FREQ like other procedures that work with classification variables automatically exclude observations with classification variables (NUM and Z) that take on a missing value.

you could also apply a WHERE to subset the data by eliminating the missing values in an earlier step.

robertrao
Quartz | Level 8


Hi,

Thanks for the reply. Below in the format if I did not use . =" "  then I would be getting . in the cross tabulation table as shown above.

I want to clarify if adding . = " " in the format is the only way to get rid of the . from the z variable(char variable) during cross tabulation?

proc format;

value char

1="ONE"

2="TWO"

. =" "   <<<<<<<<<<<-------------------------if i did not use this

;

RUN;

can i also do this;

proc freq data=have;

where z ne "." ;          /*assuming I dint use . = " " in the proc format*/

table num*z;

format num char.;

run;

Regards

ArtC
Rhodochrosite | Level 12

You could filter for the '.' using a WHERE.  However you will also need to use a LEFT function to eliminate leading blanks.  The period is actually being stored with two leading blanks.

proc freq data=have(where=(left(z) ne '.'));

robertrao
Quartz | Level 8

Hi,

Tried and tested it. Great help. Thanks for the in depth  explanation....

Regards

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1867 views
  • 7 likes
  • 4 in conversation