DATA Step, Macro, Functions and more

How to add a %Let Statement to my code?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

How to add a %Let Statement to my code?

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;


Accepted Solutions
Solution
‎12-17-2014 02:53 PM
Super User
Posts: 5,075

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

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.

View solution in original post


All Replies
SAS Employee
Posts: 232

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

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!

Super User
Posts: 17,758

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

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.

Occasional Contributor
Posts: 19

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

Thank you for help!  Much appreciated.

Solution
‎12-17-2014 02:53 PM
Super User
Posts: 5,075

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

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.

Occasional Contributor
Posts: 19

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

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

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 315 views
  • 6 likes
  • 4 in conversation