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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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).

View solution in original post

11 REPLIES 11
Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7 scb
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7 scb
Obsidian | Level 7
It will not be empty...all will be numeric.
scb
Obsidian | Level 7 scb
Obsidian | Level 7
Sorry Kurt, I don't really understand the question. Any example? Thanks.
Kurt_Bremser
Super User

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
Obsidian | Level 7 scb
Obsidian | Level 7
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
Barite | Level 11

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
Super User

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).

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
  • 11 replies
  • 1259 views
  • 0 likes
  • 4 in conversation