PROC SQL / SAS - Get every month of an active product?

Question

Hi there internet friends, I need your help. I'm an ultra noob with SQL programming and I need help with something I'm working with.

Here's what I ave right now in TABLE1

    CustNbr ProductNm  ExpirationDt(date value)
    AAA111  Product1    15MAY2017
    AAA112  Product1    21JAN2017

And here's what I would like to have :

    CustNbr Yr   Mth  ProductNm
    AAA111 2017  01   Product01
    AAA111 2017  02   Product01
    AAA111 2017  03   Product01
    AAA111 2017  04   Product01
    AAA111 2017  05   Product01
    AAA112 2017  01   Product01

Essentialy, I would like to have a monthly view of active product by customers, and not only the date that they are expiring. What kind of expression could I use to reproduce the table I want?

Thank so much for the time.

You'd better do this in your program code, not in the database. — yangsibai, Nov 01 '17 at 01:52
Definitely agree, altough I really don't what kind of expressions I could use to reproduce what I aiming to get. — THEPIGE, Nov 01 '17 at 02:07

score 4 · Answer 1 · answered Nov 01 '17 at 14:47

Here is one way to explode your data. You will need to select the starting date. The INTNX function is handy for navigating through date ranges.

data have;
input CustID $ ProdID $ ExpDate ;
attrib
  ExpDate format=date9. informat=date9.
;
datalines;
    AAA111  Product1    15MAY2017
    AAA112  Product1    21JAN2017
run;

data want;
  set have;

  * Which start date do you want ?;
  start_date = today(); * looking forward only;
  start_date = intnx('year', ExpDate, 0); * first of the year of the expiration date;
  start_date = intnx('year', today(), 0); * first of the year at run-time;

  date = start_date;
  do index = 1 by 1 while (date < ExpDate);
    year = Year(date);
    month = Month(date);
    output;
    date = intnx('month', date, 1);

    if index > 1e5 then leave; * guard against coding/data errors causing to many loops;
  end;
  format month z2.;
  keep CustID ProdID year month;
run;

score 0 · Accepted Answer · answered Nov 01 '17 at 04:36

0

use the following code

SELECT     CustNbr, YEAR(ExpirationDt) AS Yr, MONTH(ExpirationDt) AS Mnt, ProductNm
FROM         TableName

answered Nov 01 '17 at 04:36

sam7

56
2
10

If you want to show the Month name, not digit then use the following code "DATENAME" to give "July" not "7" – sam7 Nov 01 '17 at 04:41
This only selects one row per input row, whereas the OP wanted multiple output rows per input row. To do this in SQL you'd need to cross-join with a table of months/dates. – david25272 Nov 02 '17 at 02:13
I know, but "THEPIGE" only mentioned about Table1, that's why i wrote this code. – sam7 Nov 02 '17 at 06:04

PROC SQL / SAS - Get every month of an active product?

2 Answers2