topic Re: Rename numeric decimal to month label in datastep in SAS Programming

Rename numeric decimal to month label in datastep

Q1983 — Sat, 17 Apr 2021 00:10:31 GMT

%let mth4=%sysfunc(intnx(month, %sysfunc(today()),-4), monyy5.);

%let mth3=%sysfunc(intnx(month, %sysfunc(today()),-3), monyy5.);

%put &mth4 &mth3;

data have;

trans_4 = 0.9983;

trans_3 = 0.8988;

run;

I want to assign the corresponding date to trans based on the month number

So trans_4 should show as Dec20 and trans3 as Jan21

1. Would I need to convert to a character to do this?

2. What would be the way to replace the name

I tried this and receive error

data have;

trans_4 = 0.9983;

trans_3 = 0.8988;

trans_4 = "&mth4."d;

SYMBOLGEN: Macro variable MTH4 resolves to DEC20

NOTE: Line generated by the macro variable "MTH4".

Here is the code

%let mth4=%sysfunc(intnx(month, %sysfunc(today()),-4), monyy5.);

%let mth3=%sysfunc(intnx(month, %sysfunc(today()),-3), monyy5.);

%put &mth4 &mth3;

data have;

trans_4 = 0.9983;

trans_3 = 0.8988;

trans_4 = "&mth4."d;

run;

Re: Rename numeric decimal to month label in datastep

SASKiwi — Sat, 17 Apr 2021 01:33:03 GMT

Try this. You need to supply a complete date string in the form of 01Jan2021:

%let mth4=%sysfunc(intnx(month, %sysfunc(today()),-4), date9.);

%let mth3=%sysfunc(intnx(month, %sysfunc(today()),-3), date9.);

%put &mth4 &mth3;

data have;

trans_4 = 0.9983;

trans_3 = 0.8988;

trans_4 = "&mth4."d;
format trans_4 date9.;
run;

Re: Rename numeric decimal to month label in datastep

Patrick — Sat, 17 Apr 2021 02:29:00 GMT

@Q1983

"So trans_4 should show as Dec20 and trans3 as Jan21"

What should "show" as mmmyy?

1. The variable name/header when you print the data?

2. The data itself when you print it? If so then what's the logic for 0.9983 to show as Dec20?

3. Or do you really want to replace the values stored in the variables with a SAS date value (which you then format so it prints as desired)?

If you're after 2. or 3. and assuming you've got more than the two source values to start with, please explain us the logic required how to "replace" your source values with mmmYY.

Re: Rename numeric decimal to month label in datastep

Q1983 — Sat, 17 Apr 2021 02:36:14 GMT

When I do this trans_4 = "&mth4."d;
the value becomes the date. I want to keep the decimal date value and rename tran_4 wth the date name. So tran_4 should have the header name of DEC20 and keep the decimal value in the variable. Is that possible

Re: Rename numeric decimal to month label in datastep

Tom — Sat, 17 Apr 2021 02:50:47 GMT

It is not at all clear what you are trying to do. You have shown the input dataset. Show what you want the output dataset to be.

If you know what SAS code you want to generate show that code, we can then help you generate the code.

Also can you explain why you think this transformation will help you in some way?

If you want to RENAME as your subject line says then use the RENAME statement.

data want;
  set have;
  rename trans_3=&mth3 trans_4=&mth4;
run;

Re: Rename numeric decimal to month label in datastep

Patrick — Sat, 17 Apr 2021 03:02:58 GMT

@Q1983 Below some options how you could go about this.

Given that you generate the monyy names based on when you execute the code, I assume that you might want to run the code at different dates/months and though the names would change. If this is the case then may-be better only change the variable label and not the variable name as this will allow you to use a constant set of variable names in your code (else you would always have to change all the names throughout your code).


%let mth4=%sysfunc(intnx(month, %sysfunc(today()),-4), monyy5.);
%let mth3=%sysfunc(intnx(month, %sysfunc(today()),-3), monyy5.);
%put &mth4 &mth3;

/* option 1 */
data have;
  trans_4 = 0.9983;
  trans_3 = 0.8988;
run;

data want1;
  set have;
  rename trans_3=&mth3 trans_4=&mth4;
run;

/* option 2 */
data have;
  trans_4 = 0.9983;
  trans_3 = 0.8988;
run;

data want2;
  set have;
  label trans_3=&mth3 trans_4=&mth4;
run;

/* option 3 */
data have;
  trans_4 = 0.9983;
  trans_3 = 0.8988;
run;

proc datasets lib=work nolist;
  modify have;
    rename trans_3=&mth3 trans_4=&mth4
    ;
  run;
quit;

/* option 4 */
data have;
  trans_4 = 0.9983;
  trans_3 = 0.8988;
run;

proc datasets lib=work nolist;
  modify have;
    label trans_3=&mth3 trans_4=&mth4
    ;
  run;
quit;

Re: Rename numeric decimal to month label in datastep

Q1983 — Sat, 17 Apr 2021 14:01:23 GMT

Thanks your solution worked. One more issue. If you look at the code here I want to format trans_4 and trans_3 in decimal format. However I need to flip or pivot the data so that Count retains its numeric value and trans_4_&mth4 and trans_3_&mth3 are in decimal format Percent8.2 This works in the datastep however I loose the desired decimal format during proc transpose. Is there a way to retain the desired decimal format even after the proc transpose???

data have;

Category='MyCategory';

count = 7000;

trans_4 = 0.9983;

trans_3 = 0.8988;

format trans_4 trans_3 percent8.2;

run;

data want1;

set have;

rename trans_3=&mth3 trans_4=&mth4;

run;

proc transpose data=want1 out=want2 let;

var count &mth4 &mth3;

by Category;

run;

Re: Rename numeric decimal to month label in datastep

Patrick — Sun, 18 Apr 2021 01:39:11 GMT

@Q1983 Going forward please ask "unrelated" follow-up questions as a new question with a matching subject line. Not only will this make this forum more useful for people searching answers to questions already asked and resolved, it will also give your new question more attention as many people answering questions won't look into threads already marked as resolved.

You can always specifically call out someone in your new question if you want them to become aware of your new question using "@<name>". This will create a notification for the mentioned user.

Now to your question:

If you've got only numerical variables to transpose then the resulting transposed column will be numerical.

SAS formats apply on columns (variables) and not on cells. Because the variables you transpose have different formats SAS has to use a default format on the transposed column (I believe that's best32.).

In the docu for the Var statement of Proc Transpose here you can find:

To have different formatting per cell you need to convert the numerical values to strings. And for Proc Transpose to do so one of the variables must be of type character. For conversion numeric to character Proc Transpose will use the format assigned to the variable (or the default of best32. if there is no explicit format defined).

Below two options how you can get what you're after. Please note that if using the first option there can be leading blanks in the transposed values.

%let mth4=%sysfunc(intnx(month, %sysfunc(today()),-4), monyy5.);
%let mth3=%sysfunc(intnx(month, %sysfunc(today()),-3), monyy5.);
%put &mth4 &mth3;

data have;
  Category='MyCategory';
  count = 7000;
  trans_4 = 0.9983;
  trans_3 = 0.8988;
  format trans_4 trans_3 percent8.2;
run;

/** option 1 **/
data want1;
  set have;
  length _dummy $1;
  call missing(_dummy);
  rename trans_3=&mth3 trans_4=&mth4;
run;

/* add _dummy to var statement to "force" proc transpose to create a character variable */
/* for the transposed values. Drop _dummy from output as not wanted there               */
proc transpose 
    data=want1 
    out=want2(where=(upcase(_name_) ne '_DUMMY'))
    let;
  /* if desired explicit format statements allows to overwrite formats from source */
  format count comma.;
  var _dummy count &mth4 &mth3;
  by Category;
run;


/** option 2 **/
/* -l option in put statement to left align result */
data want1; 
  set have;
  count_c = put(7000,best32. -l);
  &mth3=put(trans_3,percent8.2 -l);
  &mth4=put(trans_4,percent8.2 -l);
run;

proc transpose 
    data=want1 
    out=want2
    let;
  var count_c &mth4 &mth3;
  by Category;
run;