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;
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;
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;
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.