topic Re: Missing values won't be placed in original data file using do loops (Instead of using array) in SAS/IML Software and Matrix Computations

Re: Missing values won't be placed in original data file using do loops (Instead of using array)

wzy1993 — Sun, 15 Sep 2019 13:49:26 GMT

Thanks. But what do you mean "Post it at IML forum"? I never post a IML question, this is the first time I asked it. Can you tell me what do you mean in terms of solving this problem? Thanks.

How

k93943 — Thu, 12 Sep 2019 15:07:44 GMT

Re: How to Replace Missing Values using Proc iml and do loops

PaigeMiller — Tue, 10 Sep 2019 18:15:04 GMT

I don't have a matrix to test this on, but I think this ought to work

where = loc(m=-99 or m=99);
m[where]=.;

No loops needed (in general, you don't want to use loops in IML anyway, you want to use matrix commands; the only time you would use loops is if you are developing some sort of iterative algorithm)

Re: How to Replace Missing Values using Proc iml and do loops

ballardw — Wed, 11 Sep 2019 15:33:51 GMT

If you receive values that you know are going to be set to missing, such as with an indicator code like this, you might consider reading the data into missing to begin with.

proc format library=work;
invalue myvalues
'99', '-99' = .
;
run;
/* reading raw data*/
data example;
   input v1 :myvalues. ;
datalines;
1 
2
9
99
-99
-45
45
;

Another option would be use an assignment of special missing in the format like .Z (replace the . in the invalue with .Z )that would allow you to tell the difference between missing because no value was read or the missing set due to this rule. The value is still missing for purposes of calculations but can be printed or examined.

Re: How to Replace Missing Values using Proc iml and do loops

Ksharp — Wed, 11 Sep 2019 12:08:54 GMT

Why not post it at IML forum ? since it is IML question ? @Rick_SAS is there.

data example;
   input v1 ;
   v2=v1;
datalines;
1 
2
9
99
-99
-45
45
;

proc iml;
use example;
read all var _all_ into x[c=vname];
close;

x[loc(x=99 | x=-99)]=.;

create want from x[c=vname];
append from x;
close;
quit;

Re: How to Replace Missing Values using Proc iml and do loops

Rick_SAS — Wed, 11 Sep 2019 12:38:52 GMT

As Paige and KSharp said, use the LOC function. However, never use a "naked LOC":

always check that at least one observation satisfied the search criterion.

missingIdx = loc(m=-99 | m=99);
if ncol(missingIdx)>0 then
    m[where] = .;

Missing values won't be placed in original data file using do loops (Instead of using array)

wzy1993 — Sun, 15 Sep 2019 02:15:26 GMT

Here is the code that I used trying to replace 99 and -99 with period "." using "do" loops (we are not allowed to use array in this exercise). However, the code I did only generates the replacing results in the result window, but 99 and -99 won't be replaced in the original data in the library I created. I have tried many times, but I don't why know the period won't replace 99 and -99 in the original data. I attached my code file here well. Please let me know if anyone can answer my questions. I attended my code as "hw2" and the data file as "exercise2" below. Thanks!!!!

My code for the 1st and 2nd question (Only the code for 2nd question has issues):

libname HW2 'C:\Users\Administrator\Desktop\BU_2019\BS 803\HW\HW2';
/*1.Read only the following variables into a matrix called CG: NACZZMS, NACCLMI, NACCZLMD,

NACCDFT, NACCAGEB.*/
proc iml;
/* read variables from a SAS data set into a matrix */
varNames = {"NACCZMMS" "NACCZLMI" "NACCZLMD" "NACCZDFT" "NACCAGEB"};
use hw2.exercise2; /* open data for reading */
read all var varNames into CG; /* create matrix with 5 cols */
close hw2.exercise2;
print CG[c=varNames];
proc iml;
/* read variables from a SAS data set into a matrix */
varNames = {"NACCZMMS" "NACCZLMI" "NACCZLMD" "NACCZDFT"};
use hw2.exercise2; /* open data for reading */
read all var varNames into CG; /* create matrix with 5 cols */
do j=1 to 4;
do i=1 to nrow(CG);
if CG[i,j]=-99 | CG[i,j]=99 then CG[i,j]=.;
end;
end;
print CG[c=varNames];
quit;

Re: Missing values won't be placed in original data file using do loops (Instead of using array)

Patrick — Sun, 15 Sep 2019 08:27:14 GMT

I don't know enough about Proc IML to tell if it allows you to replace a source table.

You've nicely commented your code and it doesn't say anywhere "write" or "update" - which is what's missing.

Here a link how such a write could look like:

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

And here docu sample code how to update data:

http://support.sas.com/documentation/cdl/en/imlug/67502/HTML/default/viewer.htm#imlug_worksasdatasets_sect015.htm

Re: Missing values won't be placed in original data file using do loops (Instead of using array)

Ksharp — Sun, 15 Sep 2019 11:20:40 GMT

Post it at IML forum. I remembered there is already a post like yours in IML forum.

Aren't you the same person ?

Re: Missing values won't be placed in original data file using do loops (Instead of using array)

wzy1993 — Sun, 15 Sep 2019 13:47:48 GMT

Thanks. The "print CG[c=varNames];" before "quit" gives me the replaced results, but only in SAS Result window. However, the original data in the library I created still is the same, which means missing values 99 and -99 still exist. That is the issue.

Re: Missing values won't be placed in original data file using do loops (Instead of using array)

Ksharp — Mon, 16 Sep 2019 12:19:42 GMT

https://communities.sas.com/t5/SAS-IML-Software-and-Matrix/How/m-p/587605#M4794

Re: Missing values won't be placed in original data file using do loops (Instead of using array)

Rick_SAS — Mon, 16 Sep 2019 12:46:23 GMT

I have merged the two separate threads into one thread.