topic Re: Rename dynamic month to char and order desc in SAS Programming

Rename dynamic month to char and order desc

Q1983 — Wed, 21 Apr 2021 21:15:02 GMT

data have;

length section $25

month_ $5

tot_cnt 3

month_sort 8;

input section -- month_sort;

datalines;

central JAN20 10 3

central JAN20 22 3

central FEB20 30 2

central FEB20 88 2

central MAR20 30 1

central MAR20 88 1

central APR20 30 0

central APR20 88 0

;

run;

proc transpose data=have out=want let;

id month_;

var tot_cnt;

by section;

run;

1. The report will always have a 4 month window (in this case Jan-Apr).

3. Each month will increment however we will only have the 4 month window.

So next month would be (Feb-May)

4. Since we are in April the month_sort for this month is 0. It increments based on the month elapse

3. Is there a way to dynamically rename the month results in proc transpose from

the numeric date to a character based on the month_sort? (ie Month_3 Month_2 ) and order by

the month_sort desc.

Re: Rename dynamic month to char and order desc

ballardw — Wed, 21 Apr 2021 21:32:50 GMT

Really need to show what you expect for output.

Reread your 1 through 4 and consider that we do not know what you actually want to do, there is no "report" included so references to that make no sense. And since we are in APRIL 2021 all of those dates you show are 12 to 16 months ago.

How is "month elapse" to be used, or "month_sort" no example actually provided.

Why is the proc transpose included at all?

Hint: learn to use date values instead of (hard to work with text) like "APR20".

Example:

data have;
   length section $25
      month_ $5
      tot_cnt 3
      month_sort 8;
   input section -- month_sort;
   dateval= input('01'||month_,date7.);
   format dateval MONyy.;
datalines;
central JAN20 10 3
central JAN20 22 3
central FEB20 30 2
central FEB20 88 2
central MAR20 30 1
central MAR20 88 1
central APR20 30 0
central APR20 88 0
;
run;

Second hint: make sure everyone can tell if the 20 is a day of the month or a year. Not at all obvious.

Below is an example using a specific date to select 2 dates less than 2 months prior.

proc print data=have;
   where intck('month',dateval,'01APR2020'd) lt 2;
run;

Once you have an actual date then incrementing is easy with a function like like INTNX to set boundaries or INTCK to investigate intervals.

Hint 3: Post code or log entries into text boxes opened on the forum with the </> icon to maintain formatting and prevent odd characters from creeping into code when the forum software reformats text.

Re: Rename dynamic month to char and order desc

Q1983 — Wed, 21 Apr 2021 22:20:04 GMT

@ballardw wrote:

Really need to show what you expect for output.

Reread your 1 through 4 and consider that we do not know what you actually want to do, there is no "report" included so references to that make no sense. And since we are in APRIL 2021 all of those dates you show are 12 to 16 months ago.

How is "month elapse" to be used, or "month_sort" no example actually provided.

Why is the proc transpose included at all?

Hint: learn to use date values instead of (hard to work with text) like "APR20".

Example:
data have;
   length section $25
      month_ $5
      tot_cnt 3
      month_sort 8;
   input section -- month_sort;
   dateval= input('01'||month_,date7.);
   format dateval MONyy.;
datalines;
central JAN20 10 3
central JAN20 22 3
central FEB20 30 2
central FEB20 88 2
central MAR20 30 1
central MAR20 88 1
central APR20 30 0
central APR20 88 0
;
run;
Second hint: make sure everyone can tell if the 20 is a day of the month or a year. Not at all obvious.

Below is an example using a specific date to select 2 dates less than 2 months prior.
proc print data=have;
   where intck('month',dateval,'01APR2020'd) lt 2;
run;
Once you have an actual date then incrementing is easy with a function like like INTNX to set boundaries or INTCK to investigate intervals.

Hint 3: Post code or log entries into text boxes opened on the forum with the </> icon to maintain formatting and prevent odd characters from creeping into code when the forum software reformats text.

I should have posted the proc transpose results. Here it is

section _NAME_ 20-Jan 20-Feb 20-Mar 20-Apr

central tot_cnt 22 88 88 88

Desired result is to dynamically name the header(dates)

with a character varaible based on the month

descending order. Like this

section _NAME_ Month3 Month2 Month1 Month0

central tot_cnt 22 88 88 88

Not quite sure of your statement regarding formatting <> Are you saying begin the code with <> and end it with </> as in html??

Re: Rename dynamic month to char and order desc

Tom — Wed, 21 Apr 2021 23:09:32 GMT

How did 20-JAN become MONTH3 ? Why not MONTH1 or MONTH235 ?

Re: Rename dynamic month to char and order desc

ballardw — Wed, 21 Apr 2021 23:17:41 GMT

@Q1983 wrote:

I should have posted the proc transpose results. Here it is

section _NAME_ 20-Jan 20-Feb 20-Mar 20-Apr

central tot_cnt 22 88 88 88

Desired result is to dynamically name the header(dates)

with a character varaible based on the month

descending order. Like this

section _NAME_ Month3 Month2 Month1 Month0

central tot_cnt 22 88 88 88

If you have variable in your data with the numbers like 0, 1, 2 you can use that variable as an ID variable and combine that with a PREFIX option in Proc transpose to generate the headings (though suspect with multiple records whether you get the desired one)

proc transpose data=have out=want let
   prefix=month;
   by section;
   var tot_cnt;
   id month_sort;
run;

which duplicates your last version.

If part of your problem is to get the months then the date I provided above and intck function returns intervals:

Months_previous = intck('month',dateval, '01APR2020'd);

form example. I am not going to use TODAY(), which returns the current date and might be used to generate dynamic results as the the values you provided are all apparently in 2020 and the 'months' would be 18 to 12 which does not go along with "4" in any way. You could filter you data to only use the Months_previous in the range you want.

Not quite sure of your statement regarding formatting <> Are you saying begin the code with <> and end it with </> as in html??

No. Above the message window you type in on this forum should be a row of icons. The 7th one looks like </>. Click on that an you get a text box to paste text into.

Re: Rename dynamic month to char and order desc

Patrick — Wed, 21 Apr 2021 23:32:33 GMT

Create variable month as a numerical column with a SAS Date value (count of days since 1/1/1960). In doing so you can then easily determine the max month in your data, use SAS calendar functions like intnx() to easily substract 3 months from your max date and because the internal value is now numerical and just a count of days any reporting will sort the months according to the numbers and not the strings (month names) so you get what you want.

And last but not least: You don't need to transpose your data for reporting. The Proc's for reporting will do all of this for you.

Below some sample code hopefully pointing you into the right direction.

Informat monyy5. instructs SAS to interpret the source string as a date and convert it to a SAS Date value, Format monyy5. instructs SAS to print the SAS Date value (just a number, count of days since 1/1/1960) formatted as mmmyy.

You don't need variable month_count anymore as now that the value in month is numerical it will sort as you'd expect.

And for numerical values like tot_cnt: NEVER define a length other than 8 unless you really know what you're doing. For numerical values the length does not define the numbers of digits but it defines the number of bytes used for storing the number. Anything other than the default length of 8 might lead to unexpected results (loss of numerical precision) unless you fully understand what you're doing.

/* populate month column with a SAS Data value - count of days since 1/1/1960 */
data have;
  attrib 
    section length=$25 
    month length=8 informat=monyy5. format=monyy5.
    tot_cnt length=8 
    ;
  input section -- tot_cnt;
  datalines;
central JAN20 10 3
central JAN20 22 3
central FEB20 30 2
central FEB20 88 2
central MAR20 30 1
central MAR20 88 1
central APR20 30 0
central APR20 88 0
;

/* determine max. month value in data */
data _null_;
  set have(keep=month) end=last;
  retain max_month;
  max_month=max(max_month,month);
  if last then
    do;
      /* use intnx() calendar function to substract 3 months */
      start_month=intnx('month',max_month,-3,'b');
      /* create and populate macro variables for later use */
      call symputx('max_month',max_month);
      call symputx('start_month',start_month);
      /* write max month and begin month to SAS log */
      put "Max Month   - Formatted: " max_month monyy5. 
          ", internal value count of days: " max_month 32. -l;
      put "Start Month - Formatted: " start_month monyy5. 
          ", internal value count of days: " start_month 32. -l;
    end;
run;

/* print content of macro vars (SAS Date values, count of days) */
%put &=max_month;
%put &=start_month;

/* and now do your reporting using a Proc like Report, Tabulate or whatever is suitable */
proc report data=have(where=(month>=&start_month));
....

Re: Rename dynamic month to char and order desc

Kurt_Bremser — Thu, 22 Apr 2021 09:02:26 GMT

Maxim 2: Read the Log.

Your transpose fails because of duplicate ID values per group:

 73         data have;
 74         length section $25
 75         month_ $5
 76         tot_cnt 3
 77         month_sort 8;
 78         input section -- month_sort;
 79         datalines;
 
 NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
 NOTE: The data set WORK.HAVE has 8 observations and 4 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 89         ;
 90         run;
 91         proc transpose data=have out=want let;
 92         id month_;
 93         var tot_cnt;
 94         by section;
 95         run;
 
 WARNING: The ID value "JAN20" occurs twice in derselben BY-Gruppe.
 WARNING: The ID value "FEB20" occurs twice in derselben BY-Gruppe.
 WARNING: The ID value "MAR20" occurs twice in derselben BY-Gruppe.
 WARNING: The ID value "APR20" occurs twice in derselben BY-Gruppe.
 NOTE: The above message was for the following BY group:
       section=central
 NOTE: There were 8 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.WANT has 1 observations and 6 variables.
 NOTE:  Verwendet wurde: PROZEDUR TRANSPOSE - (Gesamtverarbeitungszeit):
       real time           0.01 seconds
       cpu time            0.00 seconds

So you first need to decide how to deal with the duplicates (calculate a sum, a count, a mean?).

section	_NAME_	20-Jan	20-Feb	20-Mar	20-Apr
central	tot_cnt	22	88	88	88


Desired result is to dynamically name the header(dates)
with a character varaible based on the month
descending order. Like this

section	_NAME_	Month3	Month2	Month1	Month0
central	tot_cnt	22	88	88	88