topic Re: SAS query in SAS Programming

SAS query

scb — Mon, 17 Apr 2017 04:14:42 GMT

There are 2 datasets, old and old1 dataset. Sometime I got old which do not have the score3 variable. May I know how to check if the dataset has got score3 variable and if no, automatically set the score3 to 0.

data old;
input ID SCORE1 SCORE2;
cards;
24 100 97
28 98 87
60 100 97
65 100 98
70 99 97
40 97 99
190 100 99
196 100 100
210 98 85
;
run;

data old1;
input ID SCORE1 SCORE2 SCORE3;
cards;
24 100 97 100
28 98 87 10
60 100 97 73
65 100 98 15
70 99 97 89
40 97 99 98
190 100 99 87
196 100 100 99
210 98 85 67
;
run;

Re: SAS query

art297 — Mon, 17 Apr 2017 04:44:20 GMT

If you don't mind getting a warning and error in your log here is one way you could do it:

proc sql noprint;
  select catx(' ','data old1;set old1;retain ',name,'0;run;')
    into :test1
      from dictionary.columns
        where libname eq 'WORK' and
              memname eq 'OLD' and
              name eq 'SCORE3'
            
  ;
  select catx(' ','data old;set old;retain ',name,'0;run;')
    into :test2
      from dictionary.columns
        where libname eq 'WORK' and
              memname eq 'OLD1' and
              name eq 'SCORE3'
            
  ;
quit;

&test1.;
&test2.;

Art, CEO, AnalystFinder.com

Re: SAS query

novinosrin — Mon, 17 Apr 2017 05:20:29 GMT

%macro VarExist(ds, var);
%local rc dsid result;
%let dsid = %sysfunc(open(&ds));

%if %sysfunc(varnum(&dsid, &var)) > 0 %then %do;
%let result = 1;
%put NOTE: Var &var exists in &ds;
%end;
%else %do;
%let result = 0;
%put NOTE: Var &var not exists in &ds;

data want;

set have;

score3=0;

run;
%end;

%let rc = %sysfunc(close(&dsid));

%mend VarExist;

%VarExist(have, score3);

Regards,

Naveen Srinivasan

Re: SAS query

Kurt_Bremser — Mon, 17 Apr 2017 05:54:56 GMT

Try

data old;
score3 = 0;
set old;
run;