topic Re: Checking which columns missing from table and adding them in in New SAS User

Checking which columns missing from table and adding them in

jlin4 — Sat, 13 Mar 2021 19:52:59 GMT

Suppose I have 2 tables - Tables A and B.

/*Table A*/
data work.table_a;
input fruits$;
cards;
apple
orange
pear
banana
lemon
;
run;

/* Table B */
data work.table_b;
input apple orange banana lemon;
cards;
1 2 3 4
2 3 4 5
1 0 2 2
;
run;

Table A consists of just one column with 5 rows containing distinct string values. The number of rows and their values are fixed.

Table B consists of columns with names that are a subset of the 5 rows of Table A. In this example, Table B consists of 4 columns and the value "pear" does not belong to this subset. However, this subset may change from time to time. For example, on another occasion when this table is generated, Table B may just have 3 columns, while the values missing from the subset might be "apple" and "banana".

I am interested in finding out which are the values that are missing from the subset and subsequently, add them to Table B. The values under these columns will all be zeros.

/* Desired Output - column "pear" added to Table B */
data work.desired_output;
input apple orange pear banana lemon;
cards;
1 2 0 3 4
2 3 0 4 5
1 0 0 2 2
;
run;

Currently, I am using codes adopted from https://communities.sas.com/t5/SAS-Programming/macro-error-issue/td-p/715467.Specifically, I am typing:

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

%if &resx>0 %then %do;
%put &var exist;
%end;

%else %do;
data &ds; set &ds;
&var=0;
run;
%end;

%mend;

%VarExist(table_b, apple);
%VarExist(table_b, orange);
%VarExist(table_b, pear);
%VarExist(table_b, banana);
%VarExist(table_b, lemon);

However, such method may not be the most elegant as the real Table A that I am working on consists of 20+ rows. I would like to enquire if there is a better way to code this? Thank you.

Re: Checking which columns missing from table and adding them in

PGStats — Sat, 13 Mar 2021 20:49:34 GMT

Use a retain statement in a datastep

data fruits;
input fruit$;
cards;
apple
orange
pear
banana
lemon
;

data have;
input apple orange banana lemon;
cards;
1 2 3 4
2 3 4 5
1 0 2 2
;

proc sql;
select fruit into :fruits separated by " "
from fruits
quit;

data want;
retain &fruits. (99*0);
set have;
run;

Re: Checking which columns missing from table and adding them in

Tom — Sat, 13 Mar 2021 20:56:11 GMT

If you want to have a fixed structure then just create that structure. You could do it with code

data want;
  length apple 8 banana 8 .... ;  
  set have;
run;

or with a dataset

data want;
   set expected(obs=0) have;
run;

If you want to fill the non-existent variables with zeros instead of missing you might want to use RETAIN. Or if you also want to replace any missing in yoru actual data with zeros then perhaps wait and just replace them all with the same code.

Re: Checking which columns missing from table and adding them in

jlin4 — Sun, 14 Mar 2021 04:50:32 GMT

thank you! may i ask what does "(99*0)" do?

Re: Checking which columns missing from table and adding them in

PGStats — Sun, 14 Mar 2021 13:24:40 GMT

99*0 is the initial values list for the retained variables. The list is too long, but that doesn't matter. Missing variables will thus be created with a value of zero, a value that will never change.