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.
... View more