SAS query

[scb]

I have got the 2 datasets; test and test1, both have month1 to month 30.

May I know how to get the change the name of month1 to month30? If the date is in the range of Jan17, then month1 will be JAN17, month2 will be FEB17, month3 will be MAR17.  If the date is in the range of OCT17, then month1 will be OCT17, month2 will be NOV17, month3 will be DEC17, month4 will be JAN18.

Any idea, thanks.

DATA TEST;
INPUT CASE $ PRODUCT $ AMT DATE DATE9.;
FORMAT DATE DATE9.;
DATALINES;
PL123 PL 6000 01JAN2017
PL123 PL 6000 01JAN2017
PL172 PL 6000 06JAN2017
PL173 PL 6000 07JAN2017
PL173 PL 6000 08JAN2017
PL173 PL 6000 09JAN2017
PL173 PL 6000 01JAN2017
PL173 PL 6000 02JAN2017
;
RUN;

DATA TEST1;
INPUT CASE $ PRODUCT $ AMT DATE DATE9.;
FORMAT DATE DATE9.;
DATALINES;
PL123 PL 6000 01OCT2017
PL123 PL 6000 01OCT2017
PL172 PL 6000 06OCT2017
PL173 PL 6000 07OCT2017
PL173 PL 6000 08OCT2017
PL173 PL 6000 09OCT2017
PL173 PL 6000 01OCT2017
PL173 PL 6000 02OCT2017
;
RUN;

data TEST;
RETAIN DATE CASE PRODUCT AMT MONTH1-MONTH30;
FORMAT MONTH1-MONTH30 DOLLAR8.2;
set test;
array month{30};
run;

data TEST1;
RETAIN DATE CASE PRODUCT AMT MONTH1-MONTH30;
FORMAT MONTH1-MONTH30 DOLLAR8.2;
set test1;
array month{30};
run;

[Kurt_Bremser]

What values should go into those arrays?

And wouldn't it be better to keep a "long" format by writing 30 new records for each of those generated months, containing a date (first of month) and a value?

[RW9]

Sorry, your post is a bit unclear.  Why are there two datasets?  This is simple issue, solved straight from dataset test:

data test;
  input case $ product $ amt date date9.;
  format date date9.;
datalines;
PL123 PL 6000 01JAN2017
PL123 PL 6000 01JAN2017
PL172 PL 6000 06JAN2017
PL173 PL 6000 07JAN2017
PL173 PL 6000 08JAN2017
PL173 PL 6000 09JAN2017
PL173 PL 6000 01JAN2017
PL173 PL 6000 02JAN2017
;
run;

data want;
  set test;
  date1=date;
  date2=intnx('month',date,1);
  format date: date9.;
run;

[scb]

I would like change the change the name of the month1-month30 variable names; for example, If the date is in the range of Jan17 (in the date column, all date is in the range of Jan17), then month1 will be JAN17, month2 will be FEB17, month3 will be MAR17. If the date is in the range of OCT17, then month1 will be OCT17, month2 will be NOV17, month3 will be DEC17, month4 will be JAN18.

[Kurt_Bremser]

You should not put data (months or dates) in structure (variable names). And you still have not answered the question which values should end up in the newly created variables. Just creating lots of empty variables is an exercise in futility.

[scb]

It will not be empty...all will be numeric.

[Kurt_Bremser]

I'm NOT talking about variable types, I am asking which VALUES should end up in those variables??

[scb]

Sorry Kurt, I don't really understand the question. Any example? Thanks.

[Kurt_Bremser]

A variable that does not contain a value is useless. Just creating 30 variables without assigning values to them is stupid, IMHO. Just a waste of space with no meaning.

Ex:

data test;
array month(30) m1-m30;
run;

proc print data=test;
run;

Result:

Obs m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12 m13 m14 m15 m16 m17 m18 m19 m20 m21 m22 m23 m24 m25 m26 m27 m28 m29 m30

 1   .  .  .  .  .  .  .  .  .  .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   . 

[scb]

It is not blank. There is value in there. Please refer to the program.


DATA TEST;
INPUT CASE $ PRODUCT $ AMT DATE DATE9.;
FORMAT DATE DATE9.;
DATALINES;
PL123 PL 6000 01JAN2017
PL123 PL 6000 01JAN2017
PL172 PL 6000 06JAN2017
PL173 PL 6000 07JAN2017
PL173 PL 6000 08JAN2017
PL173 PL 6000 09JAN2017
PL173 PL 6000 01JAN2017
PL173 PL 6000 02JAN2017
;
RUN;

data TEST;
RETAIN DATE CASE PRODUCT AMT MONTH1-MONTH30;
FORMAT MONTH1-MONTH30 DOLLAR8.2;
set test;
array month{30};
do i=1 to 30;
month{i}=(((30-i)+1)/30)*amt;
end;
run;

[DanielSantos]

This is going nowhere.

Please provide a good example of the expected results.

Not clear why you have two datasets and what you want to do with it.

Daniel Santos @ www.cgd.pt

[Kurt_Bremser · Accepted solution]

These lines

do i=1 to 30;
month{i}=(((30-i)+1)/30)*amt;
end;

have been missing from all your code examples up to now.

So I'd suggest to create your new dataset like this:

data test;
input case $ product $ amt date :date9.;
format date date9.;
datalines;
PL123 PL 6000 01JAN2017
PL123 PL 6000 01JAN2017
PL172 PL 6000 06JAN2017
PL173 PL 6000 07JAN2017
PL173 PL 6000 08JAN2017
PL173 PL 6000 09JAN2017
PL173 PL 6000 01JAN2017
PL173 PL 6000 02JAN2017
;
run;

data test_new;
set test;
format month yymon7.;
do i = 1 to 30;
  month = intnx('month',date,i-1,'begin');
  amount = (((30 - i) + 1) / 30) * amt;
  output;
end;
keep case product month amount;
run;

This provides for a very simple structure, and you can use all types of date processing for your calculations (which would be hard if you had different names for variables in your datasets because of different start months).