topic Re: Check to see if variable exits and if not create variable with a value of 0 in SAS Programming

Check to see if variable exits and if not create variable with a value of 0

Haydo — Tue, 27 Jun 2023 04:34:04 GMT

Is there a way to check to see if variable exists and if not create those variable and return a value of 0 for that.

Below is a list of the variables I'l like to check to see if they exist.

I have daily data and depending on the day, the dataset could be 1,000 rows or down to 25 rows.

Will only need to return 1 row for each variable that does not exist.

CCE
CCP
CDI
CHR
COR
CRC
DCS
DOH
IMM
INT
IQI
MAI
PHO
PRC
PRI
STA
OFF
ONL
ORE
P10
PAA
PPM
PTE
RSI
STP
TIP
TOR
WPM
WSM
WTE
WWW
YEC

Re: Check to see if variable exits and if not create variable with a value of 0

Oligolas — Tue, 27 Jun 2023 17:37:31 GMT

data have;
input myVarName $;
datalines;
CCE
CCP
CDI
CHR
COR
CRC
DCS
DOH
IMM
INT
IQI
MAI
PHO
PRC
PRI
STA
OFF
ONL
ORE
P10
PAA
PPM
PTE
RSI
STP
TIP
TOR
WPM
WSM
WTE
WWW
YEC
;
run;

data test;
   length CCE DCS YEC $10;
   call missing(CCE, DCS, YEC);
   delete;
run;


PROC SQL;
   CREATE TABLE want AS
      SELECT *
      FROM have
      WHERE upcase(myVarName) NOT IN
          (SELECT NAME
           FROM sashelp.vcolumn
           WHERE libname eq 'WORK'
             AND memname eq 'TEST')
   ;
QUIT;

Re: Check to see if variable exits and if not create variable with a value of 0

Ksharp — Tue, 27 Jun 2023 11:37:58 GMT

data have;
input have $;
cards;
CCE
CCP
CDI
CHR
COR
CRC
DCS
DOH
SEX
AGE
;

data x;
 set sashelp.class;
run;


proc transpose data=x(obs=0) out=temp;
var _all_;
run;

proc sql noprint;
select name into :vnames separated by ' ' 
from
(
select upcase(have) as name from have
except
select upcase(_name_) from temp
)
quit;
data want;
 set x;
 retain &vnames. 0;
run;

Re: Check to see if variable exits and if not create variable with a value of 0

Tom — Tue, 27 Jun 2023 13:26:10 GMT

I cannot tell what you are asking for.

The Subject seems to be asking how to default variables to zero. For that you could simply use a data step with a RETAIN statement. To make it easier you might use an ARRAY also. For example if there are four variables that must exist and if they don't exist you want to create them with a value of zero you could do this:

data want;
  set sashelp.class;
  array must_have CCE CCP CDI AGE  (4*0);
  retain CCE CCP CDI AGE ;
run;

Result

But the rest of the question makes it sound like instead the question is how to test for the existence of the variables and return a list of those that are missing. So if you have a dataset with the list of variables, like the list you showed, then just compare that to the list of variable that do exist and select the ones that are not found.

Let's store the list of variable names in a variable named NAME in a dataset named EXPECT.

data expect ;
  input name $32.;
cards;
CCE
CCP
CDI
;

Now we just need to get the list of names of the variables in the input dataset, let's call that HAVE, and generate a dataset with the list of missing names, let's call that WANT.

proc contents data=have out=contents noprint; run;
proc sql;
create table want as 
select upcase(name) as name from expect
except 
select upcase(name) as name from contents
;
quit;