Initializing all array variables to zero if they are missing

[tapas_16880]

Hi,

I have a bunch of variables with their different array lengths. All are fined as PD2. or PD7.2 or PD8.2 or PD5.

At the end of the process, I want to populate the array variables to ZERO if they are having missing values. The below code I wrote but it is not working which strange. Can anyone help me. I don't want to code all array variables to do this as it will be cumbersome, some variables are daily variables and hence the array length if 366 😞

The below code is not working!!

DATA OUTDB.OUTDATA;

SET OUTDB.OUTDATA;

ARRAY CHANGE _NUMERIC_;

DO OVER CHANGE;

IF CHANGE=. THEN CHANGE=0;

END;

RUN ;

[Kurt_Bremser]

• Don't shout at the poor SAS interpreter, it can handle lowercase very well 😉
• Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to create a data step from your dataset, and post the resulting code here, according to https://communities.sas.com/t5/help/faqpage/faq-category-id/posting#posting

That will give us a clear picture of your dataset as it is.

[tapas_16880]

Thanks but extracting the code not working from the above link. Can someone tell me why the DATASTEP that I coded not working or can someone give me a piece of code that can initialize all array variables to 0 if they are missing.

[Kurt_Bremser]

Your code works, see this proof:

data have;
input id $ var1 var2 var3;
cards;
X 1 . 1
Y 2 2 2
Z . . 3
;
run;

DATA want;
SET have;
ARRAY CHANGE _NUMERIC_;
DO OVER CHANGE;
IF CHANGE=. THEN CHANGE=0;
END;
RUN ;

proc print data=want noobs;
run;

So it's got to be in the data.

When you use this macro from the link I gave you:

%macro data2datastep(dsn,lib,file,obs);
%local varlist msgtype;

%if %superq(obs)= %then %let obs=MAX;

%let msgtype=NOTE;
%if %superq(dsn)= %then %do;
   %let msgtype=ERROR;
   %put &msgtype: You must specify a data set name;
   %put;
   %goto syntax;
%end;
%let dsn=%qupcase(%superq(dsn));
%if %superq(dsn)=!HELP %then %do;
%syntax:
   %put &msgtype: &SYSMACRONAME macro help document:;
   %put &msgtype- Purpose: Converts a data set to a SAS DATA step.;
   %put &msgtype- Syntax: %nrstr(%%)&SYSMACRONAME(dsn<,lib,file,obs>);
   %put &msgtype- dsn:  Name of the dataset to be converted. Required.;
   %put &msgtype- lib:  LIBREF where the dataset resides. Optional.;
   %put &msgtype- file: Fully qulaified filename for the DATA step produced. Optional.;
   %put &msgtype-       Default is %nrstr(create_&lib._&dsn._data.sas) in the SAS default directory.;
   %put &msgtype- obs:  Max observations to include the created dataset. Optional.;
   %put &msgtype-       Default is MAX (all observations);
   %put;
   %put NOTE:   &SYSMACRONAME cannot be used in-line - it generates code.;
   %put NOTE-   Use !HELP to print these notes.;
   %return;
%end; 

%if %superq(lib)= %then %do;
    %let lib=%qscan(%superq(dsn),1,.);
    %if %superq(lib) = %superq(dsn) %then %let lib=WORK;
    %else %let dsn=%qscan(&dsn,2,.);
%end;
%let lib=%qupcase(%superq(lib));
%let dsn=%qupcase(%superq(dsn));

%if %sysfunc(exist(&lib..&dsn)) ne 1 %then %do;
   %put ERROR: (&SYSMACRONAME) - Dataset &lib..&dsn does not exist.;
   %let msgtype=NOTE;
   %GoTo syntax;
%end;

%if %superq(file)= %then %do;
   %let file=create_&lib._&dsn._data.sas;
   %if %symexist(USERDIR) %then %let file=&userdir/&file;
%end;

%if %symexist(USERDIR) %then %do;
   %if %qscan(%superq(file),-1,/\)=%superq(file) %then
      %let file=&userdir/&file;
%end;

proc sql noprint;
select Name
      into :varlist separated by ' '
   from dictionary.columns
   where libname="&lib"
     and memname="&dsn"
;
select case type
          when 'num' then 
             case 
                when missing(format) then cats(Name,':32.')
                else cats(Name,':',format)
             end 
          else cats(Name,':$',length,'.')
       end
      into :inputlist separated by ' '
   from dictionary.columns
   where libname="&lib"
     and memname="&dsn"
;
quit;

data _null_;
   file "&file" dsd;
   if _n_ =1 then do;
      put "data &lib..&dsn;";
      put @3 "infile datalines dsd truncover;";
      put @3 "input %superq(inputlist);";
      put "datalines4;";
   end;
   set &lib..&dsn(obs=&obs) end=last; 
   put &varlist @;
   if last then do;
      put;
      put ';;;;';
   end;
   else put;
run;
%mend;

like this (with my example data):

%data2datastep(have,work,$HOME/sascommunity/have.sas,3)

I get this code:

data WORK.HAVE;
  infile datalines dsd truncover;
  input id:$8. var1:32. var2:32. var3:32.;
datalines4;
X,1,,1
Y,2,2,2
Z,,,3
;;;;

in the file named.

[Ksharp]

data have;
input id $ var1 var2 var3;
cards;
X 1 . 1
Y 2 2 2
Z . . 3
;
run;
proc stdize data=have out=want missing=0 reponly;
var var1-var3;
run;

[Vijay77]

will these code work for character variables

[Kurt_Bremser]

Maxim 4. Try it.

Then look at the log.

Or follow Maxim 6 (Google Is Your Friend) to follow Maxim 1 (Read The Documentation). Do a search for "SAS proc stdize". You get a link to https://support.sas.com/documentation/cdl/en/statug/63962/HTML/default/viewer.htm#statug_stdize_sect... and will see (in the Overview section)

Overview: STDIZE Procedure

The STDIZE procedure standardizes one or more numeric variables

(emphasis by me)

---

@Vijay77 wrote:

will these code work for character variables

---

[RW9]

You can also use the missing() function rather than the =. if there are other types of missing:

data outdb.outdata;
  set outdb.outdata;
  array change _numeric_;
  do over change;
    if missing(change) then change=0;
  end;
run;

Though as @Kurt_Bremser has said, without seeing the data its impossible for us to diagnose any issue, hence why the guidance for posting a new question clearly states to post such test data.

[ballardw]

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of a dataset, the actual results and the expected results. Data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

With that out of the way use of the input data set as the output data set will cause hard to diagnose problems because the first trial execution changes in the input data set and you cannot compare the results for validity of the code result. If there were an error or miscode it may be next to impossible to reverse it so you have to go back further in the process to rebuild the source data set.

DATA OUTDB.OUTDATA;

SET OUTDB.OUTDATA;
<any code modifying variables from the incoming data>

For testing of recodes you should direct the output to a different data set.

[Astounding]

The major reason this code will fail:  perhaps your data set already contains a variable named CHANGE.  It's illegal to use the same name for both a variable and an array.

As @ballardw pointed out, it is not a good idea to use this form of a DATA step:

data outdb.outdata;

set outdb.outdata;

.....

This is dangerous, particularly when your code needs to be tested and debugged.  It is possible that your original version of the data did not contain a variable named CHANGE, but one of the tests inadvertently added such a variable to the data set.

In any event, use an array name that does not duplicate the name of an existing variable.  If that's not the problem, post the log because the log messages are always an important clue.