BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Giuliano
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Community_Help
SAS Employee

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!

Reeza
Super User

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.

Giuliano
Fluorite | Level 6

Thank you for help!  Much appreciated.

Astounding
PROC Star

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.

Giuliano
Fluorite | Level 6

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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