DATA Step, Macro, Functions and more

Initializing all array variables to zero if they are missing

Reply
Occasional Contributor
Posts: 15

Initializing all array variables to zero if they are missing

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 Smiley Sad

 

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 ;

Super User
Posts: 10,600

Re: Initializing all array variables to zero if they are missing

Posted in reply to tapas_16880

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 15

Re: Initializing all array variables to zero if they are missing

Posted in reply to KurtBremser

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.

Super User
Posts: 10,600

Re: Initializing all array variables to zero if they are missing

Posted in reply to tapas_16880

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,850

Re: Initializing all array variables to zero if they are missing

Posted in reply to tapas_16880
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;
Super User
Super User
Posts: 9,862

Re: Initializing all array variables to zero if they are missing

Posted in reply to tapas_16880

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 @KurtBremser 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.

Super User
Posts: 13,942

Re: Initializing all array variables to zero if they are missing

Posted in reply to tapas_16880

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.

 

Ask a Question
Discussion stats
  • 6 replies
  • 189 views
  • 0 likes
  • 5 in conversation