topic Re: How to get observation when variable does not exist in Advanced Programming

How to get observation when variable does not exist

pdhokriya — Wed, 13 Jan 2021 14:03:38 GMT

Hi Reader,

Could you please help me with below mentioned data where Variable C does not exist in dataset.

Need to create D variable where a = "One" and Variable C is null/not exist then consider b variable data.

Output would be like this : -

a	b	d
One	New1	New1
Two	New2	New2
Three	New3	New3

Thank you in advance.

Regards,

Priyanka

Re: How to get observation when variable does not exist

PeterClemmensen — Wed, 13 Jan 2021 14:06:58 GMT

So you want to create theh variable D only if the variable C does not exist?

Re: How to get observation when variable does not exist

Kurt_Bremser — Wed, 13 Jan 2021 14:12:21 GMT

There is no variable c in your dataset, so it can't be used in a condition. It "can't even be null".

If you need to make code conditional upon the mere presence of a variable in a dataset, query DICTIONARY.COLUMNS and run code conditionally (%IF-%THEN-%DO-%END).

%let name=X;

proc sql noprint;
select name into :name
from dictionary.columns
where libname ="WORK" and memname = "HAVE" and upcase(name) = "C";
quit;

data want;
set have;
%if &name. = X
%then do;
if a = "One" then d = b;
%end;
run;

Re: How to get observation when variable does not exist

Panagiotis — Wed, 13 Jan 2021 14:23:44 GMT

I am a bit unclear. The C column does not exist at all, so you can't use it in a condition.

Re: How to get observation when variable does not exist

Shmuel — Wed, 13 Jan 2021 14:31:38 GMT

Next code will do what you want:

data want;
  set have;
        retain c;  /* if c does not exist it will be added with null value */
        if a='One' then do;
           if missing(c) then d=b;
           else d=c;
        end;
run;

Re: How to get observation when variable does not exist

pdhokriya — Wed, 13 Jan 2021 17:54:43 GMT

This will give Note: Variable c is uninitialized. Which will not be recommended.

Re: How to get observation when variable does not exist

pdhokriya — Wed, 13 Jan 2021 17:56:44 GMT

If C does not exits thn only i will move to b. else will consider C data only.

Re: How to get observation when variable does not exist

SuCheeTay — Wed, 13 Jan 2021 18:14:51 GMT

Taking @Kurt_Bremser code and updated to this should give you what you need. Variable d=b when c is either missing from the table or it's value is missing.

%let name=X;

proc sql noprint;
select name into :name
from dictionary.columns
where libname ="WORK" and memname = "HAVE" and upcase(name) = "C";
quit;

data want;
set have;
%if &name = X
%then %do;
d = b;
%end;
%else %do;
if c=" " then d=b;
%end;
run;

Re: How to get observation when variable does not exist

Shmuel — Wed, 13 Jan 2021 18:17:01 GMT

Alternative way to check weather variable C exists:

Data test;
   dsid = open('have');  /*open file to check */
   if dsid then do;
      _varnum = varnum(dsid,'C');   /* check does C exist */
      putlog _varnum=;
   end;
   dsid = close(dsid);
run;
/***  
    _varnum = 0 for non existing C variable in HAVE dataset
    otherwise _varnum > 0
***/

Re: How to get observation when variable does not exist

pdhokriya — Fri, 15 Jan 2021 08:25:29 GMT

Thank you for the solution, but I can except only 1 solution 🙂

Re: How to get observation when variable does not exist

pdhokriya — Fri, 15 Jan 2021 08:26:13 GMT

Thank you for your help. @Shmuel AZURITE

Re: How to get observation when variable does not exist

pdhokriya — Fri, 15 Jan 2021 08:26:43 GMT

Thank you so much.