Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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

[ Edited ]

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;


Accepted Solutions
Solution
‎03-01-2017 05:52 PM
Occasional Contributor
Posts: 18

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

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;

View solution in original post


All Replies
Frequent Contributor
Posts: 75

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

Can you provide some sample data to work with?
Super User
Posts: 17,912

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

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;

 

 

Solution
‎03-01-2017 05:52 PM
Occasional Contributor
Posts: 18

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

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 150 views
  • 0 likes
  • 3 in conversation