Hello,
I am new to SAS and I am creating a program to compare pricing month over month for the variable "SOC". The code I have so far is below. I need to add a %Let macro statement to the code below so I can enter the month I am looking up. Any help would be appreciated. I am not sure how to do this or where in the code to add it. I am also tring to ensure I do not have any duplicate "SOC" variables. Not sure if using the DISTINCT function will work. I am pulling necessary information from several tables and joining them. Now I need a way to automate this program so it can run it month over month.
PROC SQL;
CREATE TABLE work.mobpp1 AS
SELECT DISTINCT soc, ban, subscriber_no
FROM bmlappo.service_agreement
WHERE subscriber_no ~= '0000000000'
AND expiration_date IS NULL OR expiration_date >= '15-DEC-2014'd
GROUP BY soc, ban, subscriber_no;
QUIT;
PROC SQL;
CREATE TABLE work.mobpp2 AS
SELECT DISTINCT soc, ban, subscriber_no, feature_code, SUM (ACTV_AMT) AS soc_price
FROM bmlappo.charge
WHERE sys_creation_date >= '15-DEC-2014'd
AND ACTV_REASON_CODE = 'R'
AND FEATURE_CODE = 'STD'
GROUP BY soc, ban, subscriber_no, feature_code, actv_amt;
QUIT;
PROC SQL;
CREATE TABLE work.mobpp3 AS
SELECT t1.soc, t1.ban, t1.subscriber_no, t2.feature_code, t2.soc_price
FROM work.mobpp1 t1, work.mobpp2 t2
WHERE t1.ban = t2.ban
AND t1.subscriber_no = t2.subscriber_no;
QUIT;
PROC SQL;
CREATE TABLE work.mobpp4 AS
SELECT DISTINCT ban, account_type, account_sub_type
FROM bmlappo.billing_account;
QUIT;
PROC SQL;
CREATE TABLE work.mobpp5 AS
SELECT t1.*, t2.account_type, t2.account_sub_type
FROM work.mobpp3 t1, work.mobpp4 t2
WHERE t1.ban = t2.ban;
QUIT;
PROC SQL;
CREATE TABLE work.mobpp6 AS
SELECT DISTINCT soc, soc_description, service_type, soc_status, datepart (sale_eff_date)format=mmddyy10. AS sale_eff_date, datepart(sale_exp_date)format=mmddyy10. AS sale_exp_date, product_type
FROM bmlrwork.soc
WHERE (expiration_date IS NULL OR expiration_date >= '15-DEC-2014'd)
AND product_type IN ('CEL','A');
QUIT;
PROC SQL;
CREATE TABLE work.mobpp7 AS
SELECT t1.*, t2.soc_description, t2.service_type, t2.soc_status, t2.sale_eff_date, t2.sale_exp_date
FROM work.mobpp5 t1, work.mobpp6 t2
GROUP BY soc, feature, ban;
QUIT;
Given that you are relatively new to SAS, I would recommend a two-step, keep-it-simple approach.
Step 1 may already be complete: Get a version of your code that works for one particular date with no macro language.
Step 2 adds macro language, but in the simplest way possible. Use a %LET statement that assigns the entire date string, such as:
%let mydate = '15-DEC-2014'd;
Then within your code replace all occurrences of '15-DEC-2014'd with &mydate. That combination will perform simple text substitution wherever &mydate appears.
Good luck.
Hi Giuliano,
Welcome to Communities on SAS. Glad you are here.
I am going to move your question to the Macro group so you have a better chance of getting your question seen and answered. Be sure to mark it "answered" or "helpful answer" if someone replies. Thank so much!
Add it at the top.
%let month=DEC;
Then find wherever DEC is in your code and replace it with &month. Make sure that if it's in quotes, its in double quotes not single quotes.
Thank you for help! Much appreciated.
Given that you are relatively new to SAS, I would recommend a two-step, keep-it-simple approach.
Step 1 may already be complete: Get a version of your code that works for one particular date with no macro language.
Step 2 adds macro language, but in the simplest way possible. Use a %LET statement that assigns the entire date string, such as:
%let mydate = '15-DEC-2014'd;
Then within your code replace all occurrences of '15-DEC-2014'd with &mydate. That combination will perform simple text substitution wherever &mydate appears.
Good luck.
Thank you, that's help me a great deal.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.