XLS export

With ODS EXCELXP or ODS EXCEL, you may get some fancy looking as you can add style or format. However, I prefer the native Excel output without any decoration. Please note that we can write only one dataset at one time.

Below is the sample code:

libname xlsout xlsx '.\out.xls';

data xlsout.a;
    set sashelp.class;
run;

libname xlsout clear;

Does keywords _NUMERIC_/_CHARACTER_ represent all numeric/character variables?

Array is my favorite tool. With reserved keywords _NUMERIC_/_CHARACTER_, it can handle dynamic variables list. However, they may not represent all numeric/character variables. When it is in different position of DATA step, it will have different variables list. The codes below shows that array with _NUMERIC_/_CHARACTER_ represents only pre-defined variables. The post-defined variables will not be included anyway.

data _null_;
    array num0{*} _numeric_;

    putlog "Initial numeric array:";
    do i=1 to dim(num0);
        putlog i= num0[i]=;
    end;

    a = 1;
    b= 2;

    array num1{*} _numeric_;
    putlog "First numeric array:";
    do i=1 to dim(num1);
        putlog i= num1[i]=;
    end;

    c = 3;

    array num2{*} _numeric_;
    putlog "Second numeric array:";
    do i=1 to dim(num2);
        putlog i= num2[i]=;
    end;
run;

Result:
Initial numeric array:
First numeric array:
i=1 i=1
i=2 a=1
i=3 b=2
Second numeric array:
i=1 i=1
i=2 a=1
i=3 b=2
i=4 c=3

Oracle DDL export from SAS - get_ddl truncation issue

When we grab the complete DDL from Oracle, please increase DBMAX_TEXT value to avoid the truncation issue.

proc sql;
 connect to ORACLE 
        (DBMAX_TEXT=5000 READBUFF=32767 user=xxx password=xxx);

    create table ddl as
    select txt 
    from connection to ORACLE 
    (
        select dbms_metadata.get_ddl('TABLE', table_name) txt
        from user_tables
    );

    disconnect from ORACLE;
quit;

$w. vs. $CHARw.

$w. is the default informat for character variable. It will convert single period to a blank automatically. To fix it, we can use $CHARW. alternatively.

Below are the difference in SAS doc:
The $w. informat is almost identical to the $CHARw. informat. However, $CHARw. does not trim leading blanks nor does it convert a single period in an input field to a blank, while $w. does both.

data num;
   infile datalines dlmstr="|" missover;

   length x $10;
   attrib y length=$10 informat=$char10.;
   length z $10;

   input x y z;
   put x= y= z=; * y is period while z is blank;
   datalines;
1|.|.
;

IN operator

It is my favorite operator. Generally it is used to compare one expression on the left of the operator to a list of values on the right. However, you may find the two tricks are helpful.
#1. To search some variables, we have to use array.

data _null_;
    a=11;
    b=2;
    c=3;
    array vars a b c;
    /* if 1 in (a, b, c) then put "OK"; ERROR: we can not search the list of variables directly. */
    if 1 in vars then put "OK";
run;

#2. The modifier ":" can work well with IN operator.

data _null_;
    a='11';
    b='2';
    c='3';
    array vars a b c;
    if '1' in: vars then put "OK";
    if '111' in: vars then put "OK";
run;

Customized buttons

I have created several customized buttons. Hopefully they are helpful.

Close ViewTable - next "viewtable:";end; next "viewtable:";end; next "viewtable:";end; next "viewtable:";end; next "viewtable:";end; wpgm
Submit Macro magic code - gsubmit "*'; *""; *); */; %mend; run;"; wpgm
New Save - file "d:\temp\saswork\%left(%sysfunc(datetime(), b8601dt.)).sas";
Open Work library location: gsubmit 'systask command "explorer %sysfunc(pathname(work))" nowait;'

Listing all files that are located in a specific directory (update)

I prefer the codes independent on OS. It may save a lot of time to maintain. Based on SAS Note 25074, I have created the codes at below:

%macro readdir(indir=, outdsn=);
    data &outdsn (keep=name infoname infoval);
       rc=filename("mydir", "&indir");
       did=dopen("mydir");

        if did > 0 then do;
            memcnt = dnum(did);
            do i=1 to memcnt;
                name = dread(did, i);
                
                rc = filename("myfile", catx("/", "&indir", name));
                fid = fopen("myfile");
                if fid > 0 then do;
                    infonum=foptnum(fid);
                    do j=1 to infonum;
                        infoname=foptname(fid, j);
                        infoval=finfo(fid, infoname);
                        output;
                    end;
                end;
                else do;
                    msg = sysmsg();
                    put msg;
                end;
                rc = fclose(fid);
            end;
        end;
        else do;
            msg=sysmsg();
            put msg;
        end;

        rc = dclose(did);
        rc = filename("mydir");
    run;
%mend;

%readdir(indir=C:\test, outdsn=out)