BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Haydo
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

3 REPLIES 3
Oligolas
Barite | Level 11
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 -

Ksharp
Super User
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;
Tom
Super User Tom
Super User

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

Tom_0-1687871192470.png

 

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;

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 869 views
  • 3 likes
  • 4 in conversation