topic How to add a %Let Statement to my code? in SAS Programming

How to add a %Let Statement to my code?

Giuliano — Wed, 17 Dec 2014 19:09:42 GMT

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;

Re: How to add a %Let Statement to my code?

Community_Help — Wed, 17 Dec 2014 19:27:13 GMT

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!

Re: How to add a %Let Statement to my code?

Reeza — Wed, 17 Dec 2014 19:40:02 GMT

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.

Re: How to add a %Let Statement to my code?

Astounding — Wed, 17 Dec 2014 19:53:52 GMT

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.

Re: How to add a %Let Statement to my code?

Giuliano — Wed, 17 Dec 2014 19:59:44 GMT

Thank you for help! Much appreciated.

Re: How to add a %Let Statement to my code?

Giuliano — Wed, 17 Dec 2014 20:00:27 GMT

Thank you, that's help me a great deal.