topic proc freq output to data set in SAS Programming

proc freq output to data set

Ronein — Sun, 26 May 2024 07:44:21 GMT

Hello

I want to calculate for each numeric variable-

number of rows with null values

number of rows with zero values

number of rows with positive values

number of rows with negative values that not null

I want that the output will be out to a data set

I don't see the desired output

I also want to ask about the user format I have defined.

Is null value not included in both groups??

. ='Missing'
Low-<0='Neg'

I want that null be only in first group (. ='Missing')


data have;
input x y z R $;
cards;
1 2 0 a
3 0 -2 b
. 3 3 c
. . . r
4 4 4 e
4 4 0 f
-1 -1 -1 g
;
run;

proc format;
value  numeric_Fmt 
. ='Missing' 
0='Zero'
0<-High='POS'
Low-<0='Neg'
;
run;
 
proc freq data=have; 
format _NUMERIC_ numeric_Fmt.;
tables _NUMERIC_ /out=want missing missprint nocum nopercent;
run;

Re: proc freq output to data set

PaigeMiller — Sun, 26 May 2024 10:51:08 GMT

Try this:

.z<-<0='Neg'

Re: proc freq output to data set

Ronein — Sun, 26 May 2024 11:41:37 GMT

Error

ERROR: Cannot mix missing and nonmissing values in the same range: Z<-<0.



proc format;
value  numeric_Fmt 
. ='Missing' 
0='Zero'
0<-High='POS'
.z<-<0='Neg'
;
run;

Re: proc freq output to data set

Ronein — Sun, 26 May 2024 11:54:41 GMT

What about the problem that I cannot see the desired output in the wanted data set?

I want to see for each numeric variable -

nr rows with zero value

nr rows with pos value

nr rows with neg value

nr rows with null value

Re: proc freq output to data set

ballardw — Sun, 26 May 2024 12:02:46 GMT

You are asking to create VARIABLES named Missing, Neg, Pos and zero. So assigning a format alone will not do that.

If this were my project:

data have;
input x y z R $;
cards;
1 2 0 a
3 0 -2 b
. 3 3 c
. . . r
4 4 4 e
4 4 0 f
-1 -1 -1 g
;
run;

proc format;
value  numeric_Fmt 
. ='Missing' 
0='Zero'
0<-High='POS'
Low-<0='Neg'
;
run;

data helpful;
   set have;
   array a(*) _numeric_;
   do i=1 to dim(a);
      var=vname(a[i]);
      value=put(a[i],numeric_fmt. -L);
      output;
   end;
run;

Proc freq data=helpful noprint;
   tables var*value/out=counts nocum nopercent;
run;

proc transpose data=counts out=want (drop=_name_ _label_) ;
  by var notsorted;
  id value;
  var count;
run;

Re: proc freq output to data set

Ronein — Sun, 26 May 2024 13:29:51 GMT

Thanks. What about my question regarding format of null value? Is null value not included in 2 different groups??
. ='Missing'
Low-<0='Neg'

As far as I know null in sas is negative

Re: proc freq output to data set

PaigeMiller — Sun, 26 May 2024 13:41:02 GMT

@Ronein wrote:

What about the problem that I cannot see the desired output in the wanted data set?

I want to see for each numeric variable -

nr rows with zero value

nr rows with pos value

nr rows with neg value

nr rows with null value

This was answered by @Reeza in a question you asked on April 17. https://communities.sas.com/t5/SAS-Programming/proc-freq-one-way-table-for-multiple-vars-export-to-data-set/m-p/924730#M363982.

As far as the format problem is concerned, you could replace .z with some lower number that the data will never exceed, such as -999999. Or if that is absolutely unknown, you could use:

proc format;
value numeric_Fmt 
._-.z ='Missing' 
0='Zero'
0<-High='POS'
other='Neg'
;
run;

Please stop showing us partial logs. When there is an ERROR, we want to see the log for that PROC or DATA step, including the code as it appears in the log, including all errors and warnings, that are in the log for that PROC or DATA step. We have asked you to stop doing this before, and you continue to do it.

Re: proc freq output to data set

Tom — Sun, 26 May 2024 16:16:18 GMT

@Ronein wrote:
Thanks. What about my question regarding format of null value? Is null value not included in 2 different groups??
. ='Missing'
Low-<0='Neg'

As far as I know null in sas is negative

No. I suspect you are thinking about how the inequality operator works. But PROC FORMAT has its own rules. In PROC FORMAT range of values definitions LOW is the the smallest non-missing value. So missing is not included in your 'Neg' range.

But you have another problem because you do not assign any display label for any of the other 27 possible missing values. So .Z will display as 'Z' instead of as 'Missing' since it is not included in any of your ranges.

Try a format like this instead and all 28 missing values will be labeled as Missing.

proc format;
 value sign low-<0 = 'Negative'
       0 = 'Zero'
       0<-high = 'Positive'
       other = 'Missing'
 ;
run;