topic Re: how to check if macro variable is in a column,if it's not, insert rows from/into a different tab in SAS Procedures

how to check if macro variable is in a column,if it's not, insert rows from/into a different table?

Datino — Tue, 28 Feb 2017 23:05:05 GMT

I have

-table A with a single column with month values like: "jan2016", "feb2017", "aug2014"...

-table B with the columns: month, name, like: "jan2016", "john smith"...

-table C with the same columns as table B

-a macro variable with a month value stored in it, like ""jan2016"

How do I:

1. check whether the value stored in the macro variable is in table A

2. if it is, terminate program, if it is not then

3. insert all the rows in table B into table C?

I would prefer to do this with Proc SQL, although data steps are ok too if the result is the same as SQL.

Thanks in advance.

Edit: adding sample data

data tableA;
input month $;
datalines;
jan2016
feb2017
aug2014
dec2010
jan2013
;

data tableB;
input month $ name $;
datalines;
apr2000 john_smith
apr2000 karl
apr2000 lidia
apr2000 anna
apr2000 francis
;

data tableC;
input month $ name $;
datalines;

nov2001 amelie
nov2001 juan
nov2001 joe
nov2001 norman
nov2001 katie
;

%let month=nov2001;

Re: how to check if macro variable is in a column,if it's not, insert rows from/into a different tab

nehalsanghvi — Tue, 28 Feb 2017 22:42:37 GMT

Can you provide some sample data to work with?

Re: how to check if macro variable is in a column,if it's not, insert rows from/into a different tab

Reeza — Tue, 28 Feb 2017 22:46:00 GMT

1. Sample data?

2. Are your dates character fields or SAS dates (numeric with a date format). This defines how the comparison will have to work, though you could get around it easily.

This is untested but the sketch of the idea of how this could works. There are multiple ways to do each step.

%macro do_whatever(month_p);

%*check if value exists in table;

proc sql noprint;
select month_name into :var_check
from tableA
where month_name = "&month_p";
quit;

%if &var_check  ne %str() %then %do;

proc append data=tableB base=TableA;
run;

%end;

%mend;

Re: how to check if macro variable is in a column,if it's not, insert rows from/into a different tab

Datino — Wed, 01 Mar 2017 22:52:00 GMT

For some reason, Reeza's code worked some times and threw errors others. I got the following code to run, it's probably not the best solution, but it works.

proc sql noprint;
create table work.recs_to_add as
select *
from
tableB
where
not exists (select * from tableC where month="&month");
insert into tableC
select * from work.recs_to_add;
quit;