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

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 missing and non-missing.JPG

 

proc freq data = ac.test;

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

run;

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;

 

 

--
Paige Miller

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
jeremy4
Quartz | Level 8
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
Diamond | Level 26

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.

--
Paige Miller
jeremy4
Quartz | Level 8
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
Diamond | Level 26

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;

 

 

--
Paige Miller
jeremy4
Quartz | Level 8
Thanks for your help!
Ksharp
Super User
proc sql;
create table want as
select age,nmiss(weight) as missing,n(weight) as non_missing
 from sashelp.class
  group by age;
quit;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 7 replies
  • 11836 views
  • 3 likes
  • 3 in conversation