topic Re: Change column format in SAS Programming

Change column format

harshpatel — Mon, 14 Dec 2020 09:08:52 GMT

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

Re: Change column format

PeterClemmensen — Mon, 14 Dec 2020 09:16:09 GMT

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

Re: Change column format

harshpatel — Mon, 14 Dec 2020 09:18:21 GMT

Hi,

Actually i want to do in actual data set

But you can guide me in both the way if possible

Re: Change column format

yabwon — Mon, 14 Dec 2020 09:40:06 GMT

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

Re: Change column format

harshpatel — Mon, 14 Dec 2020 11:12:56 GMT

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

Re: Change column format

yabwon — Mon, 14 Dec 2020 11:16:58 GMT

Replace this part:

array vars _numeric_ ;

with something like this:

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

Bart

Re: Change column format

harshpatel — Mon, 14 Dec 2020 11:28:19 GMT

Thanks,

But now description column is showing (- .)

Please find the below screenshot

Re: Change column format

yabwon — Mon, 14 Dec 2020 11:50:24 GMT

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?

Re: Change column format

harshpatel — Mon, 14 Dec 2020 13:19:59 GMT

Hi,

It is showing below error

Re: Change column format

yabwon — Mon, 14 Dec 2020 13:28:53 GMT

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;

Re: Change column format

Tom — Mon, 14 Dec 2020 14:31:10 GMT

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;

Re: Change column format

harshpatel — Tue, 15 Dec 2020 04:39:59 GMT

Thanks,

Great, it works for me

Re: Change column format

harshpatel — Tue, 15 Dec 2020 04:50:17 GMT

Thanks,

Superb, it works for me