Giuliano Tracker

Re: How to Change a Variable Name Label to a date

Giuliano — Mon, 30 Mar 2015 16:45:54 GMT

I am joining several tables to get this report. I am using a master table to pull my last months numbers, using max date. I am running my query again to get current months rate. I am adding a Case function to compare the 2 columns. After I run my compare, I want to change the labels to dates if possible.

How to Change a Variable Name Label to a date

Giuliano — Mon, 30 Mar 2015 16:32:13 GMT

Hello,

I am creating a report every month and 2 of my columns are labelled "Current Months Rate" and "Previous Months Rate" I was wondering if there was an easy way to actually display the date or even the Year and Month in place of "Current Months Rate" or "Previous Months Rate". So instead of "Current Months Rate" I would like the column to be labelled 03/01/2015 or 2015_03. And I would need these columns to be relabelled automatically every month.

Any help would be much appreciated.

Thanks.

Re: Need help combining 2 observations on PROC TRANSPOSE

Giuliano — Mon, 19 Jan 2015 22:27:57 GMT

Ok thank you. I will give that a shot.

Re: Need help combining 2 observations on PROC TRANSPOSE

Giuliano — Mon, 19 Jan 2015 22:16:25 GMT

And just forget about the Transpose all together? Do a Merge maybe?

Re: Need help combining 2 observations on PROC TRANSPOSE

Giuliano — Mon, 19 Jan 2015 22:04:12 GMT

I know but if I move the effective_date to a VAR statement then it throws everything else off. I have to somehow keep this exact format and join my 2 obervations in 1 line.

Need help combining 2 observations on PROC TRANSPOSE

Giuliano — Mon, 19 Jan 2015 21:32:29 GMT

Hi,

I am using a PROC APPEND to gather my data monthly. I am then using PROC TRANSPOSE to change the format of my data. Each month I will have a new column added to the end of my spreadsheet to compare pricing month to month. I ran a test to compare what happens when a price changes from one month to the next. Everything looks good except I when there was a pricing change from one month to the next it created a new line. I just want one line with the price from month to month. I pasted below to show you what it looks like now along with the last part of my code. I just need everything on one line, not two. Not sure if I need to modify my TRANPOSE or insert a new DATA STEP afterwards to combine the 2. I still want the old rate to appear in last month columns and I still want the new rate to compare in this months column. Everything else with the program works perfectly. Any suggestions?

PROC SORT data=work.mobpp4;
BY soc DESCENDING effective_date;
RUN;

PROC TRANSPOSE DATA = rashare.gf_temp_mobility_RC_jan19
    out=work.longf (DROP = _name_ _Label_)
    PREFIX = EXTRACT_PERIOD_
    LET;
    BY SOC Feature_Code soc_description effective_date expiration_date Extract_Date;
    ID Extract_Year_Month;
    VAR RATE;
RUN;

SOC	Feature_Code	Description	effective_date	Extract_Date	Exctract_Period_Y2015_M02	Extract_Period_Y2015_M01
0MGS1OP	UTGSTD	Sharing option	8/7/2008	1/19/2015	.	20
0MGS1OP	UTGSTD	Sharing option	8/17/2008	1/19/2015	30	.

Message was edited by: Giuliano Filoso

Re: Program to rename, change and merge table month over month.

Giuliano — Fri, 16 Jan 2015 13:14:44 GMT

Its is simply a product that I am comparing month over month. I am looking at the pricing for these products and I am trying to determine if the rate is changing from month to month.

Adding checkpoints and Restart from checkpoints

Giuliano — Tue, 13 Jan 2015 19:09:18 GMT

Hello,

I need to add checkpoints at each stage and a restart from checkpoint to the code below. I will be running this code monthly and if it fails at any point, I will need to restart it at the point in which it failed, instead of rerunning the entire program again. I would like to be notified as well if there is an error. Would anyone be able to assist me with this?

PROC SQL;
CREATE TABLE work.mobpp1 AS
SELECT DISTINCT soc, feature_code, rate,
    datepart(effective_date)format=mmddyy10. AS effective_date,
    datepart(sys_creation_date)format=mmddyy10. AS sys_creation_date,
    datepart(expiration_date)format=mmddyy10. AS expiration_date,
    "&sysdate"d as Extract_Date format= MONYY7.
FROM bmlrwork.pp_rc_rate
WHERE feature_code NOT IN ('PNETRM');
QUIT;

PROC SQL;
CREATE TABLE work.mobpp2 AS
SELECT DISTINCT soc, soc_description, product_type, service_provider
FROM bmlrwork.soc;
QUIT;

PROC SQL;
CREATE TABLE work.mobpp3 AS
SELECT DISTINCT t1.soc, t1.feature_code, t1.rate, t1.effective_date, t1.sys_creation_date, t1.expiration_date,
t2.soc_description, t2.product_type, t2.service_provider, t1.Extract_Date
FROM work.mobpp1 AS t1
LEFT JOIN work.mobpp2 AS t2
ON t1.soc = t2.soc;
QUIT;

PROC SORT data=work.mobpp3;
BY soc DESCENDING effective_date;
RUN;

DATA work.mobpp4;
SET work.mobpp3;
BY soc DESCENDING effective_date;
IF first.soc THEN OUTPUT;
RUN;

DATA RASHARE.GF_TEMP_MOBILITY_RC_JAN13 RASHARE.GF_TEMP_IPBB_RC_JAN13;
set work.mobpp4;
IF service_provider in ('ALL','MOBL')THEN OUTPUT RASHARE.GF_TEMP_MOBILITY_RC_JAN13;
ELSE OUTPUT RASHARE.GF_TEMP_IPBB_RC_JAN13;
RUN;

Convert Data string to Date String MONYY7.

Giuliano — Fri, 09 Jan 2015 18:53:01 GMT

I am running the code below. I added the sysdate to my first 2 tables. The sysdate keeps coming out as a character string. I would like to change it to a date string and only display the month and year (MONYY7.) Can anyone please assist me with modifiying my current code or adding a data step to convert the character string to a date string and display month and year only. ex the date right now is diplayed as 09JAN15 which is formated as CHAR7. I would like to display the date as JAN2015.

Thank you,

PROC SQL;
CREATE TABLE work.mobpp1 AS
SELECT DISTINCT soc, feature_code, rate,
    datepart(effective_date)format=mmddyy10. AS effective_date,
    datepart(sys_creation_date)format=mmddyy10. AS sys_creation_date,
    datepart(expiration_date)format=mmddyy10. AS expiration_date,
    "&sysdate" as Extract_Date
FROM bmlrwork.pp_rc_rate
WHERE expiration_date =.;
QUIT;

DATA work.mobpp1;
INPUT Extract_Date $7.;
FMTExtract_Date= INPUT(Extract_Date,monyy7.);
RUN;

PROC SQL;
CREATE TABLE work.mobpp2 AS
SELECT DISTINCT soc, soc_description, product_type, "&sysdate" AS Extract_Date
FROM bmlrwork.soc
WHERE expiration_date =.;
QUIT;

PROC SQL;
CREATE TABLE work.mobpp3 AS
SELECT DISTINCT t1.soc, t1.feature_code, t1.rate, t1.effective_date, t1.sys_creation_date, t1.expiration_date,
t2.soc_description, t2.product_type, t2.Extract_Date
FROM work.mobpp1 AS t1
LEFT JOIN work.mobpp2 AS t2
ON t1.soc = t2.soc;
QUIT;

Message was edited by: Giuliano Filoso

Re: Merge monthly data - PROC APPEND

Giuliano — Thu, 08 Jan 2015 14:01:24 GMT

Ok perfect, thank you Reeza.

Re: Merge monthly data - PROC APPEND

Giuliano — Thu, 08 Jan 2015 13:43:12 GMT

Ok, so you are saying that I should use PROC REPORT after I do my PROC APPEND to display the results needed? And forget about about using PROC TRANPOSE?

Merge monthly data - PROC APPEND

Giuliano — Wed, 07 Jan 2015 21:55:31 GMT

Hello,

I have created a table which I will need to run on a montly basis to compare pricing changes month over month on products and services, and to detemine when a new product or service is introduced. I will be running my report at the beginning of each month and then performing a PROC APPEND to merge the products and services. The first month I will need all information brought in. After that I just need the updated pricing for that current month and also any new products or services that were introduced. I need to have my read vertically in columns similar to the ones I pasted below. I understand that I will need to Transpose my data afterwards to change the view from horizontal to vertical. The SOC, Feature and Description columns will remain constanst however I will need to add a new column ever month, for ex. Rate_201501, Rate_201502, Rate_201503. every I will have approx 50k record in my table. Most months, the Products and Services will not change, just the rate might change.

I am assuming that I will probably need to insert another column to my current table so that I can insert the Month/Year that I am running the table for. I have no run date field in my current table. When I run multiple tables now, it will list all my results horizontally. I have no way of distinguishing which variables are from which month. My question is, how do I add the date month over month to my current table and have it display as it does in the second table? Maybe I'm going about this wrong but any input would be appreciated.

These are the fields on my current table. Right now my data is displayed horizontally.

SOC

FEATURE_CODE

RATE

SOC_DESCRIPTION

PRODUCT_TYPE

This is how I want my table to look as I am adding the months in. I need to Transpose my data so that it displays vertically in this format. And I need to add a new header each month. Not sure how I can achive this.

SOC

Feature

Feature Description

Rate_201412

Rate_201501

Rate_201502

Rate_201503

Re: Program to rename, change and merge table month over month.

Giuliano — Tue, 06 Jan 2015 20:44:40 GMT

I will need to store the changes in my master data. However I only need to report on the current month and the previous month. My master will have all months.

Re: Program to rename, change and merge table month over month.

Giuliano — Tue, 06 Jan 2015 20:35:32 GMT

Thank you for the response. When you say that I should store the effective date concerned as a SAS date value, how would I do that? Just to give you more info, the report will be run at the beginning of each month. I want to merge the reports from the last month and the current month to deteremine what, if anything, has changed. What I am looking for is a pricing change in an existing product, or if a new product appears on the list that didnt appear there the month before.

Re: Program to rename, change and merge table month over month.

Giuliano — Tue, 06 Jan 2015 16:34:40 GMT

Thank you for your response. I'll try this method and see if it works.

Program to rename, change and merge table month over month.

Giuliano — Mon, 05 Jan 2015 22:03:29 GMT

Hello,

I am fairly new to SAS. I created a table to give me all billing rates for various products or features that I am looking at. I need to run this table once a month to compare the billing rate for the products and features month over month. I would need a program or DATA Step to execute the table and compare the list month over month. Every month I will need to highlight any new or deleted products and features. I will also need to highlight any pricing changes for existing products and features. My Master Table will need to contain the billing rates for all months going forward. I will need to add a new column every month to show the billing rate for that month and if there were any changes. The header will also have to be insterted automaticall with the current month. I will also need to create a separate monthly report with only the current month and the previous months billing rate along with any changes, additions or deletions. For example, this month I would show the billing rate for December and the billing rate for January only, with a comparison of the 2 months. Next month I would compare January and February. I pasted of an example of my headers below. The number of products or features I will be comparing would exceed 30k records per month.

I am not even sure where to start with this. Any assistance would be greatly appreciated.

SOC

Feature

Feature Description

Rate_201412

Rate_201501

Rate_201502

Rate_201503

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.

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.

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;