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
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;
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;
- Cheers -
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.