Change column format

[harshpatel]

Hi All,

I have a columns name like below:

Description 201201  201202  201203  201204  201205  201301  201302  201303  201304..........etc.

Now i want to change column name dynamically from 201201 to 201304

into following format

Jan-2012  Feb-2012 Mar-2012 Apr-2012 May-2012 Jan-2013 Feb-2013 Mar-2013 Apr-2013.......etc.

Thanks,

Harsh

[PeterClemmensen]

Do you want to do this in your actual SAS data or just in Proc Report?

[harshpatel]

Hi,

Actually i want to do in actual data set

But you can guide me in both the way if possible

[yabwon]

Hi,

You can't have digit as a first symbol of variable name (unless you have the `validvarname=any` option enabled). 

If validvarname is set to `any` you will have to call to your variables names with quotes ended with `n` in your code, e.g. "201201"n.

Hyphen (`-`) is also not allowed in standard session setting, I would reccomend replace it with underscore(`_`), other wise you will have to use the "..."n approach.

With that "warning" said, your code could be something like:

options validvarname=ANY;
data work.have;
"201201"n = 1;
"201202"n = 1;
"201203"n = 1; 
"201204"n = 1; 
"201205"n = 1; 
"201301"n = 1; 
"201302"n = 1; 
"201303"n = 1; 
"201304"n = 1;
run;

data _null_;
  set work.have;
  array vars _numeric_ ;

  length monyear $ 8 replace $ 32767;
  do over vars;
    monyear = put(input(vname(vars),YYMMN6.),MONYY7.);
    monyear = substr(monyear,1,3) !! "-" !! substr(monyear,4,4);
    put monyear =;

    replace = cat(strip(replace), " '", strip(vname(vars)), "'n = '", monyear, "'n ");
  end;

  call symputX("replace",replace,"G");
  stop;
run;

title "before";
proc print data = work.have;
run;

proc datasets lib=work nolist;
modify have;
  rename 
    &replace.
  ;
run;
quit;

title "after";
proc print data = work.have;
run;

Bart

[harshpatel]

Thanks for your response,

 But when i am submiting below program with your guidance then i am getting below error.

data have;
retain ID Description '201201'n '201202'n '201203'n 0;
run;

One of my column name id automatically renamed as (- .) , but it should named id only

[yabwon]

Replace this part:

array vars _numeric_ ;

with something like this:

array vars '201201'n -- '201203'n;

Bart

[harshpatel]

Thanks,

But now description column is showing (- .)

Please find the below screenshot

[yabwon]

I see correct result. Did you run it like that? 

data have;
retain ID Description '201201'n '201202'n '201203'n 0;
run;

data _null_;
  set work.have;
  array vars _numeric_ ;

  length monyear $ 8 replace $ 32767;
  do over array vars '201201'n -- '201203'n;
    monyear = put(input(vname(vars),YYMMN6.),MONYY7.);
    monyear = substr(monyear,1,3) !! "-" !! substr(monyear,4,4);
    put monyear =;

    replace = cat(strip(replace), " '", strip(vname(vars)), "'n = '", monyear, "'n ");
  end;

  call symputX("replace",replace,"G");
  stop;
run;

title "before";
proc print data = work.have;
run;

proc datasets lib=work nolist;
modify have;
  rename 
    &replace.
  ;
run;
quit;

title "after";
proc print data = work.have;
run;

Are the variables in that exact order in the data set: ID Description '201201'n '201202'n '201203'n ?

What Log says?

B

[harshpatel]

Hi,

It is showing below error

[yabwon · Accepted solution]

Sorry, my mistake. I pasted it wrong. This is the code that should be executed:

data have;
retain ID Description '201201'n '201202'n '201203'n 0;
run;

data _null_;
  set work.have;
  array vars '201201'n -- '201203'n;

  length monyear $ 8 replace $ 32767;
  do over vars;
    monyear = put(input(vname(vars),YYMMN6.),MONYY7.);
    monyear = substr(monyear,1,3) !! "-" !! substr(monyear,4,4);
    put monyear =;

    replace = cat(strip(replace), " '", strip(vname(vars)), "'n = '", monyear, "'n ");
  end;

  call symputX("replace",replace,"G");
  stop;
run;

title "before";
proc print data = work.have;
run;

proc datasets lib=work nolist;
modify have;
  rename 
    &replace.
  ;
run;
quit;

title "after";
proc print data = work.have;
run;

[harshpatel]

Thanks,

Great, it works for me

[Tom]

Why do you want to go from one set of bad names to another?   

Anyway let's setup an example dataset since you didn't provide one.

options validvarname=any;
data have;
 length ID $12  Description $20 '201201'n-'201212'n  8;
run;

So to convert those YYYYMM strings into actual DATE values use the YYMMN informat.  Then to generate the MON-YYYY strings you can use the MONNAME and YEAR formats.  You can use the DICTIONARY.COLUMNS metadata view to find the names.  

proc sql noprint;
select catx('-',put(input(name,yymmn6.),monname3.),put(input(name,yymmn6.),year4.)) as date
     , catx('=',nliteral(name),nliteral(calculated date))
  into :renames,:renames separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='HAVE'
    and 0=notdigit(trim(name))
;
quit;

Resutls:

 %put &=renames ;
RENAMES="201201"N="Jan-2012"N "201202"N="Feb-2012"N "201203"N="Mar-2012"N "201204"N="Apr-2012"N
"201205"N="May-2012"N "201206"N="Jun-2012"N "201207"N="Jul-2012"N "201208"N="Aug-2012"N
"201209"N="Sep-2012"N "201210"N="Oct-2012"N "201211"N="Nov-2012"N "201212"N="Dec-2012"N

Now you use that list in a RENAME statement (or the RENAME= dataset option) to rename the variables in the next step you run.  Or to rename in place use PROC DATASETS to modify the dataset and change the names without re-writing the actual data.

proc datasets lib=work nolist;
  modify have;
    rename &renames ;
  run;
quit;

But even with the new names your dataset is still not very useful.  Instead you should transpose the data so that the DATE values are stored as data and not as metadata (variable NAME).  For example you could call the new variable MONTH since it contains month values. Then give the COL1 that PROC TRANSPOSE will create to store the values you have the variables with dates as names a name that actually reflects what the values mean.

proc transpose data=have out=want;
  by id description;
run;
data want ;
  set want;
  month= input(_name_,yymmn6.);
  format month monyy7. ;
  rename col1 = Real_Name ;
run;

[harshpatel]

Thanks,

Superb, it works for me