topic Re: Send this IML output to dataset instead of result window in SAS/IML Software and Matrix Computations

Send this IML output to dataset instead of result window

ProcWes — Thu, 07 Dec 2017 19:47:38 GMT

This is either impossible or really easy, but today's the first day I've ever looked at IML. I am using this code to analyze a dataset to see what percent is missing for each variable. I need the results in a dataset instead of a result window.

proc iml;
use one;
read all var _NUM_ into x[colname=nNames]; 
n = countn(x,"col");
nmiss = countmiss(x,"col");
 
read all var _CHAR_ into x[colname=cNames]; 
close one;
c = countn(x,"col");
cmiss = countmiss(x,"col");
 
/* combine results for num and char into a single table */
Names = cNames || nNames;
rNames = {"    Missing", "Not Missing"};
cnt = (cmiss // c) || (nmiss // n);
print cnt[r=rNames c=Names label=""];
quit;

Re: Send this IML output to dataset instead of result window

Reeza — Thu, 07 Dec 2017 19:51:24 GMT

Here's a blog post with instructions:

https://blogs.sas.com/content/iml/2011/04/18/writing-data-from-a-matrix-to-a-sas-data-set.html

@ProcWes wrote:

This is either impossible or really easy, but today's the first day I've ever looked at IML. I am using this code to analyze a dataset to see what percent is missing for each variable. I need the results in a dataset instead of a result window.
proc iml;
use one;
read all var _NUM_ into x[colname=nNames]; 
n = countn(x,"col");
nmiss = countmiss(x,"col");
 
read all var _CHAR_ into x[colname=cNames]; 
close one;
c = countn(x,"col");
cmiss = countmiss(x,"col");
 
/* combine results for num and char into a single table */
Names = cNames || nNames;
rNames = {"    Missing", "Not Missing"};
cnt = (cmiss // c) || (nmiss // n);
print cnt[r=rNames c=Names label=""];
quit;

Re: Send this IML output to dataset instead of result window

ProcWes — Thu, 07 Dec 2017 19:55:43 GMT

Thanks for the reply. I was unable to apply that blog to this code. Good effort though.

Re: Send this IML output to dataset instead of result window

Reeza — Thu, 07 Dec 2017 19:58:19 GMT

Post what you've tried then and we can point out where it's incorrect.

Re: Send this IML output to dataset instead of result window

ProcWes — Thu, 07 Dec 2017 20:00:33 GMT

proc iml;
use one;
read all var _NUM_ into x[colname=nNames]; 
n = countn(x,"col");
nmiss = countmiss(x,"col");
 
read all var _CHAR_ into x[colname=cNames]; 
close one;
c = countn(x,"col");
cmiss = countmiss(x,"col");
 
/* combine results for num and char into a single table */
Names = cNames || nNames;
rNames = {"    Missing", "Not Missing"};
cnt = (cmiss // c) || (nmiss // n);
print cnt[r=rNames c=Names label=""];
create wes;
append from cnt;
close wes;
quit;

Re: Send this IML output to dataset instead of result window

Reeza — Thu, 07 Dec 2017 20:59:47 GMT

create wes var {cnt};
append;
close wes;

This is closer but probably not what you want. I would reshape it using T as indicated in the comments here, where I'm assuming your code also originates from.

https://blogs.sas.com/content/iml/2011/09/19/count-the-number-of-missing-values-for-each-variable.html

You should also note the comments, PROC FREQ is considered faster this solution is not optimized within IML.

Re: Send this IML output to dataset instead of result window

ProcWes — Thu, 07 Dec 2017 21:01:27 GMT

I figured it out on my own. Posting this for anyone who finds this post later. This would work to analyze a set to find missing rates of all variables, but it runs out of memory for my large data.

proc iml;
use one;
read all var _NUM_ into x[colname=nNames]; 
n = countn(x,"col");
nmiss = countmiss(x,"col");
 
read all var _CHAR_ into x[colname=cNames]; 
close one;
c = countn(x,"col");
cmiss = countmiss(x,"col");
 
/* combine results for num and char into a single table */
Names = cNames || nNames;
VarName = {"Missing", "Not Missing"};
cnt = (cmiss // c) || (nmiss // n);
print cnt[r=VarName c=Names label=""];
create wes from cnt[rowname=VarName colname=names];
append from cnt[rowname=VarName];
close wes;
quit;

Re: Send this IML output to dataset instead of result window

ProcWes — Thu, 07 Dec 2017 21:02:28 GMT

Proc Freq can't output all of the variables to a dataset, which is the original issue raised here.

Re: Send this IML output to dataset instead of result window

Reeza — Thu, 07 Dec 2017 21:04:54 GMT

Why not? Same out of memory error?

@ProcWes wrote:
Proc Freq can't output all of the variables to a dataset, which is the original issue raised here.

Re: Send this IML output to dataset instead of result window

ProcWes — Thu, 07 Dec 2017 21:13:17 GMT

No, because Proc Freq doesn't create a table that looks like

Name Var1 Var2

Missing 4 12

NotMissing 8 0

Re: Send this IML output to dataset instead of result window

Reeza — Thu, 07 Dec 2017 21:21:53 GMT

Not by default but it easily can. In this one the format is transposed, from what you indicated, but PROC TRANSPOSE can easily fix that.

https://gist.github.com/statgeek/2de1faf1644dc8160fe721056202f111

Re: Send this IML output to dataset instead of result window

Rick_SAS — Thu, 07 Dec 2017 22:19:31 GMT

See "Writing data from a matrix to a SAS data set" for an example, or the SAS/IML documentation for the CREATE statement.

Re: Send this IML output to dataset instead of result window

ProcWes — Thu, 07 Dec 2017 22:21:23 GMT

How do I mark this as not helpful? This is the same blog Reeza posted earlier.

Re: Send this IML output to dataset instead of result window

Reeza — Thu, 07 Dec 2017 22:32:57 GMT

@ProcWes wrote:
How do I mark this as not helpful? This is the same blog Reeza posted earlier.

Seriously? Ever heard of the saying, if you don't have something nice to say don't say anything at all.

You realize Rick is the person who originally posted those blogs and takes the time to write them in the first place?