proc freq missing and non-missing values

[jeremy4]

Hi,

Question 1

I have a dataset, ac.test, where I am trying to find the number of missing and non-missing values for the "Value" field based on each result from the "Time_on_the_record" field. Please can someone help me with the code required so that the results can replicate the screenshot below?

Question 2

Along with a proc freq, would it also be possible to create a proc sql table by grouping "Value" and "Time_on_the_record", then counting the volume of accounts for each group so that the table can be exported into Excel?

Note: ""Time_on_the_record" and "Value" are both numeric variables. The screenshot below is only an example of the ac.test dataset, which has over 100,000 observations.

proc freq data = ac.test;

    tables ........................... ;

run;

[PaigeMiller]

PROC FREQ has a MISSING option.

proc freq data=ac.test;
     tables time_on_record*value/missing;
run;

Question 2: yes, I think this is possible, what is the problem?

Also, the PROC FREQ output can be sent to a SAS dataset, and that could then be exported to Excel.

[jeremy4]

Thanks for your reply! I've only kept those two variables in my dataset (which has over 100,000 observations) for the proc freq, but there is an error message that reads: "the results are large (58596155 bytes) and could take a long time and a large amount of system resources to add to the project". When I click "Yes" to continuing, there is a blank proc freq in the "Results".

Is there an alternative method that is able to achieve the output shown in my screenshot?

[PaigeMiller]

Well, I think the problem is that time_on_record is continuous, and so there will be LOTS of levels when you run PROC FREQ. I would advise some sort of "binning" of the time_on_record to reduce the number of different levels that PROC FREQ will find. Binning could be simple, such as round to the nearest integer, or round to the nearest 5, or it could be more advanced. I don't have your data, and even if I had the data I don't have the understanding of the actual problem that you have, so I can't be more specific.

[jeremy4]

Hi, I've only kept records where time_on_record = 0, 1, 2, 3, 4, 5 or 6, which leaves about 80,000 observations in the dataset. As I've been asked to individually check the number of missing and non-missing values of "Value" for each of the "time_on_record" bands, is there an alternative method to get the output shown from what I've put in the screenshot?

[PaigeMiller · Accepted solution]

Okay, I see the mistake. I was misled by your decision to use PROC FREQ. This is not a job for PROC FREQ, this is a job for PROC SUMMARY. Why? Because PROC FREQ would consider each value of variable VALUE to need its own column in the output table, while PROC SUMMARY will just count missings and count non-missings, resulting in just two columns.

proc summary data=ac.test nway;
    class time_on_the_record;
    var value;
    output out=want n=n_nonmissing nmiss=n_missing;
run;

[jeremy4]

Thanks for your help!

[Ksharp]

proc sql;
create table want as
select age,nmiss(weight) as missing,n(weight) as non_missing
 from sashelp.class
  group by age;
quit;