topic Re: strange code in SAS Programming

strange code

HeatherNewton — Sat, 03 Dec 2022 13:24:34 GMT

data _null_;
    if exist ('WDATA.TUEF_EA_H') then stop;
    else call execute ('proc sql; create table WDATA.TUEF_EA_H as select * from SDATA.TUEF_EA where 1=2;quit;');
run;

in the above code, does it mean if it cannot find WDATA.TUEF_EA_H in folder WDATA, it will create one? What does 1=2 means?

Re: strange code

PaigeMiller — Sat, 03 Dec 2022 13:53:56 GMT

Sometimes, all you have to do is look at the documentation. Here is the documentation for the EXIST function.

EXIST Function

Verifies the existence of a SAS library member within a currently assigned SAS data library.

In PROC SQL, you can refer to variables by their position in the SELECT clause. So 1=2 is testing to see if the first variable in the SELECT clause is equal to 2. In my opinion, this is really a poor way to program anything, unless your goal is to make the code as unreadable as possible. I would advise not doing this.

Re: strange code

Tom — Sat, 03 Dec 2022 16:03:54 GMT

Yes. 1=2 is an obviously FALSE condition.

Not sure why the data step is generating SQL code. The programmer obviously knows how to write data step code so why not generate a data step? It would be shorter and much less confusing than the SQL code.

data _null_;
    if exist ('WDATA.TUEF_EA_H') then stop;
    else call execute ('data WDATA.TUEF_EA_H;set SDATA.TUEF_EA(obs=0);run;');
run;

Re: strange code

HeatherNewton — Sat, 03 Dec 2022 16:56:15 GMT

So it is just creating an empty dataset?

Re: strange code

HeatherNewton — Sat, 03 Dec 2022 16:59:31 GMT

What is more strange is the next bit

Proc sql;
Insert into wdata.tuef_ea_h
Select * from sdata.tuef_ea:
quit;

Re: strange code

Tom — Sat, 03 Dec 2022 17:20:19 GMT

So the combination to append from SDATA to WDATA.

So if your version of SAS is recent enough (or the code is inside a macro) then you can just do this:

data wdata.tuef_ea_h;
  set
%if %sysfunc(exist(wdata.tuef_ea_h)) %then %do;
    wdata.tuef_ea_h
%end;
    sdata.tuef_ea
  ;
run;

Or if wdata.tuef_ea_h might get really large perhaps you want to do this to save some time.

%if %sysfunc(exist(wdata.tuef_ea_h)) %then %do;
  proc append base=wdata.tuef_ea_h data=sdata.tuef_ea;
  run;
%end;
%else do;
  data wdata.tuef_ea_h;
    set sdata.tuef_ea ;
  run;
%end;

Re: strange code

FreelanceReinh — Sat, 03 Dec 2022 23:11:49 GMT

Or just

proc append base=wdata.tuef_ea_h data=sdata.tuef_ea;
run;

as PROC APPEND knows what to do if the BASE dataset does not exist:

NOTE: BASE data set does not exist. DATA file is being copied to BASE file.

Re: strange code

SASKiwi — Sat, 03 Dec 2022 23:40:00 GMT

This is a bit off-topic, but I hope your recent posts highlighting obscure coding practices provide a lesson to those coders who delight in doing this, often for no good reason.

If your code is to be used and maintained by others, write it in such a way that they can easily understand it. Exhibit 1 has to be: where 1=2;

Re: strange code

Quentin — Sun, 04 Dec 2022 18:17:55 GMT

I've seen WHERE 1=2 elsewhere. Especially in languages, like SAS, which don't have a boolean variable type.

In SAS, I would probably do this as WHERE 0 ; Curious if you would think WHERE 0 is obscure, or a reasonable pattern?

Re: strange code

FreelanceReinh — Sun, 04 Dec 2022 19:53:44 GMT

I would also prefer WHERE 0, but not in this particular case, as the clean PROC SQL way to create an empty dataset with the structure of another dataset does not require any WHERE condition:

proc sql;
create table wdata.tuef_ea_h
like sdata.tuef_ea;
quit;