topic Re: Replacing missing column variables in SAS Programming

Replacing missing column variables

jacksonan123 — Wed, 04 Nov 2020 19:36:04 GMT

I have the following data set:

Param col2 treat subn2

ka1 0.41 ATES .

ka2 1.00 ATES .

ka1 . BREF 0.64

ka2 . BREF 1.00

The data set has missing values for ka1 and ka2 for subn2. What I want to do is to replace the missing ka1 and ka2 values in subn2 with the values ka1 and ka2 values under col2. I tried the inserted SAS code. I got the following syntax error in the log.

I think that my logic is okay but it may not be. Can someone tell me if my logic seems okay and if so how do I correct the syntax error and if the logic is faulty what would be better?

[Data new;
482 set Xall_final10_2;
483 retain temp1 temp2;
484 if ka1 in (col2 ne.) then temp1=ka1;
____
22
76
485 if ka2 in (col2 ne.) then temp2=ka2;
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, iterator, (.

ERROR 76-322: Syntax error, statement will be ignored.]


Data new;
set Xall_final10_2;
retain temp1 temp2;
if ka1 in (col2 ne.) then temp1=ka1;
if ka2 in (col2 ne.) then temp2=ka2;

if ka1 in (subn2=.) then ka1=temp1;
if ka2 in (subn2=.)  then ka2=temp2;
run;

Re: Replacing missing column variables

ballardw — Wed, 04 Nov 2020 19:55:52 GMT

The IN operator requires a list of values. No functions, no variable names, no operations, just values:

if numeric something like:

if x in ( 1 2 3).

If the comparison is character the values are in quotes

in name in ('Fred' 'John' 'Mary')

in ( col2 ne .) violates two of the rules, Col2 is apparently a variable and "ne" is a comparison operation.

Please provide data in a useable form, like a data step. You do not show a "missing Ka1" and values of ka1 are not "under col2".

If you want to test if a variable has a missing value easiest is: missing(variablename) which returns a numeric 1/0 for "true" or "false"

Re: Replacing missing column variables

Shmuel — Wed, 04 Nov 2020 19:57:42 GMT

The combination of in (subn2 = ) or in (subn2 ne ) is erroneous

What you probably mean to do is:

if param = "ka1" and col2 ne . then temp1 = param; else 
if param = "ka2" and col2 ne . then temp2 = param; else
if col2 = . then do;
  if param = "ka1" then col2 = temp1; 
                             else col2 = temp2;
run;

Re: Replacing missing column variables

jacksonan123 — Wed, 04 Nov 2020 22:21:35 GMT

I tried your code and got the following log which indicates that ka1 is
being read as invalid numeric data. Can you tell me why

NOTE: Character values have been converted to numeric values at the places
given by: (Line):(Column).

487:30 488:13

NOTE: Invalid numeric data, temp1='ka1' , at line 487 column 30.

Param=ka1 COL2=. Treat=BREF subn2=0.649437562 subj1=. subj2=1.914463759
subj3=. subj4=. subj5=. subj6=. subj7=. subj8=. subj9=.

subj10=. subj11=. subj12=. subj13=. subj14=. subj15=. subj16=. subj17=.
subj18=. subj19=. subj20=. subj21=. subj22=. subj23=.

subj24=. subj25=. subj26=. subj27=. subj28=. subj29=. subj30=. subj31=.
subj32=. subj33=. subj34=. subn1=. subn3=. subn4=. subn5=.

subn6=. subn7=. subn8=. subn9=. subn10=. subn11=. subn12=. subn13=. subn14=.
subn15=. subn16=. subn17=. subn18=. subn19=. subn20=.

subn21=. subn22=. subn23=. subn24=. subn25=. subn26=. subn27=. subn28=.
subn29=. subn30=. subn31=. subn32=. subn33=. subn34=. i=35

temp1=ka1 temp2=ka2 _ERROR_=1 _N_=8

I also tried to remove the double quotes "ka1" then the log read:

NOTE: Character values have been converted to numeric values at the places
given by: (Line):(Column).

487:28 488:13

NOTE: Variable ka1 is uninitialized.

NOTE: Variable ka2 is uninitialized.

Re: Replacing missing column variables

Shmuel — Thu, 05 Nov 2020 00:18:07 GMT

Just need define temp1 and temp2 variables as char type by assigning $<length>:

Data new;
set Xall_final10_2;
length temp1 temp2 $3;
retain temp1 temp2;
if param = "ka1" and col2 ne . then temp1 = param; else 
if param = "ka2" and col2 ne . then temp2 = param; else
if col2 = . then do;
  if param = "ka1" then col2 = temp1; 
                             else col2 = temp2;
run;