BookmarkSubscribeRSS Feed
d0816
Quartz | Level 8

Hi,

I need help renaming column names.

I have around 100 columns like this...

79. Q1 201680. Q2 201688. Q3 201682. Q4 201683. Q1 201784. Q2 201785. Q3 201786. Q4 201787. Q1 201889. Q2 201890. Q3 201891. Q4 2018

..which I want to change to..

2016-Q12016-Q22016-Q32016-Q42017-Q12017-Q22017-Q32017-Q42018-Q12018-Q22018-Q32018-Q4

 

I want to remove the preceding number (which btw may not be in order) and format the quarter as shown above.

 

How can I do this?

Appreciate any help.

 

Thanks.

12 REPLIES 12
PaigeMiller
Diamond | Level 26

Please give us more context and background about this table. Surely, you know that a SAS variable name cannot begin with a number and can't have dash in it.

--
Paige Miller
d0816
Quartz | Level 8

@PaigeMiller 

yes you are right.  I gave you excel column names instead of sas column names. clearly I was not thinking.  

 

To give you the context, I have a dataset like below.

IDFirst Event Begin QuarterLast Event Begin QTRLast Event End QTR_01__Q3_1996_02__Q4_1996_03__Q1_1997_04__Q2_1997_05__Q3_1997_06__Q4_1997_07__Q1_1998_08__Q2_1998_09__Q3_1998_10__Q4_1998_11__Q1_1999_12__Q2_1999_13__Q3_1999_14__Q4_1999_15__Q1_2000_16__Q2_2000_17__Q3_2000_18__Q4_2000_19__Q1_2001_20__Q2_2001_21__Q3_2001_22__Q4_2001_23__Q1_2002_24__Q2_2002_25__Q3_2002_26__Q4_2002_27__Q1_2003_28__Q2_2003_29__Q3_2003_30__Q4_2003_31__Q1_2004_32__Q2_2004_33__Q3_2004_34__Q4_2004_35__Q1_2005_36__Q2_2005_37__Q3_2005_38__Q4_2005_39__Q1_2006_40__Q2_2006_41__Q3_2006_42__Q4_2006_43__Q1_2007_44__Q2_2007_45__Q3_2007_46__Q4_2007_47__Q1_2008_48__Q2_2008_49__Q3_2008_50__Q4_2008_51__Q1_2009_52__Q2_2009_53__Q3_2009_54__Q4_2009_55__Q1_2010_56__Q2_2010_57__Q3_2010_58__Q4_2010_59__Q1_2011_60__Q2_2011_61__Q3_2011_62__Q4_2011_63__Q1_2012_64__Q2_2012_65__Q3_2012_66__Q4_2012_67__Q1_2013_68__Q2_2013_69__Q3_2013_70__Q4_2013_71__Q1_2014_72__Q2_2014_73__Q3_2014_74__Q4_2014_75__Q1_2015_76__Q2_2015_77__Q3_2015_78__Q4_2015_79__Q1_2016_80__Q2_2016_82__Q4_2016_83__Q1_2017_84__Q2_2017_85__Q3_2017_86__Q4_2017_87__Q1_2018_88__Q3_2016_89__Q2_2018_90__Q3_2018_91__Q4_2018_92__Q1_2019_93__Q2_2019
11998-Q12017-Q42018-Q2$0.00$220.00$0.00$0.00$132.00$138.00$0.00$487.00$0.00$0.00$0.00$0.00$0.00$919.00$0.00$0.00$634.00$557.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$739.00$180.00$0.00$0.00$529.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$33.00$0.00$5,577.00$5,577.00$5,467.00$3,959.00$3,698.00
22001-Q32017-Q42018-Q2$0.00$67.00$150.00$0.00$0.00$696.00$0.00$0.00$1,007.00$1,307.00$2,824.00$103.00$1,918.00$2,150.00$1,229.00$86.00$0.00$0.00$1,179.00$1,629.00$2,679.00$2,659.00$551.00$1,550.00$3,552.00$909.00$894.00$3,099.00$2,087.00$3,648.00$878.00$1,388.00$2,450.00$2,613.00$694.00$0.00$0.00$1,494.00$3.00$300.00$1,088.00$2,171.00$688.00$4,193.00$3,856.00$4,393.00$4,744.00$4,271.00$3,654.00$4,407.00$4,468.00$4,449.00$1,849.00$2,746.00$2,852.00$2,415.00$1,956.00$1,027.00$0.00$3,141.00$3,987.00$3,775.00$0.00$1,619.00$3,053.00$3,230.00$2,009.00$4,508.00$6,575.00$8,747.00$6,005.00$4,886.00$4,382.00$3,898.00$3,481.00$1,636.00$2,343.00$5,689.00$3,519.00$3,539.00$4,791.00$3,635.00$3,968.00$3,686.00$0.00$6,687.00$3,877.00$8,302.00$8,302.00$7,478.00$15.00$5,605.00
31996-Q12018-Q12018-Q2$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$534.00$1,623.00$0.00$0.00$0.00$0.00$121.00$912.00$0.00$0.00$392.00$387.00$0.00$1,749.00$312.00$2,186.00$2,211.00$1,503.00$0.00$627.00$2,013.00$1,582.00$1,146.00$974.00$2,438.00$1,493.00$3,083.00$4,418.00$2,753.00$2,652.00$4,118.00$3,849.00$2,573.00$3,030.00$3,291.00$2,925.00$3,519.00$2,358.00$2,340.00$4,104.00$3,618.00$4,212.00$1,112.00$3,962.00$3,031.00$4,057.00$361.00$0.00$0.00$957.00$3,360.00$1,620.00$0.00$2,347.00$3,332.00$3,817.00$553.00$478.00$2,929.00$3,419.00$2,310.00$2,671.00$2,866.00$3,456.00$3,224.00$2,951.00$2,927.00$2,566.00$0.00$0.00$3,241.00$0.00$2,228.00$2,956.00$3,619.00$3,619.00$3,788.00$2,962.00$4,186.00

 

I need to add three columns like below where I need to match the quarter with the column quarter and pick that value in that matching column quarter like below. If there is no matching column quarter I need to identify that and so put -$1.00 (no value is actual dataset is negative). I thought I will start with making the column names same but clearly I was not thinking.

Amount in First Begin QTRAmount in Last Begin QTRAmount in Last End QTR
$0.00$0.00$5,577.00
$2,679.00$0.00$8,302.00
-$1.00$2,228.00$3,619.00

 

How can I add these three columns?

 

Thank you.

PaigeMiller
Diamond | Level 26

So the table you now show, is that the Excel table, or the SAS data set? Does it get created somehow earlier in your code/process flow, or is this a data set / Excel file that is provided to you and you're stuck with it?

 

As a general rule, these types of analyses are much easier handled when you have a long data set rather than a wide one. In the long data set, your _01___Q3_1996 becomes an identifier rather than a column heading. So, first thing I would do is transpose this data set, but we still need a lot more background and detail in order to provide specific code. Also, are the values 1998-Q1 and so on in the columns on the left actual numeric SAS date values, or are they character strings?

--
Paige Miller
d0816
Quartz | Level 8

I am provided excel file in a wide-form and I am importing it to sas. So I am stuck with it. so,  01. Q3 1996 in excel becomes _01__Q3_1996 in sas.

 

First Event Begin Quarter, Last Event Begin QTR, Last Event End QTR got imported as Type Char, Length 7, Format $7., and Informat $5. .  

 

_01___Q3_1996 and most of the columns got imported as Type Char, Length 5, Format $5., and Informat $5. .Some columns got imported as Type Num, Length 8, Format NLMNY15.2, and  no Informat.

PaigeMiller
Diamond | Level 26

Well, isn't that lovely? A difficult problem made worse by an absurd layout of the data. Please convey my dislike to the people who provided the table.

_01___Q3_1996 and most of the columns got imported as Type Char, Length 5, Format $5., and Informat $5. .Some columns got imported as Type Num, Length 8, Format NLMNY15.2, and  no Informat.

 

And of course, this makes the problem even harder. 

 

Please provide the SAS data set in DATA step code, by following these instructions: 

How to create a data step version of your data AKA generate sample data for forums

--
Paige Miller
d0816
Quartz | Level 8

@PaigeMiller 

I am not being able to generate the sas data set following the method. I tried the codes including the edits. I am getting errors.

ballardw
Super User

@d0816 wrote:

@PaigeMiller 

I am not being able to generate the sas data set following the method. I tried the codes including the edits. I am getting errors.


When getting errors with code:

 

Copy from the LOG all of the procedure or data step and the errors, notes and messages. Paste into a code box opened with the forum's {i} icon. The code box will preserve formatting of any diagnostic messages.

 

That way we have some possibility of addressing the issue.

d0816
Quartz | Level 8
@ballardw

I ran this code...

filename tagset url "https://gist.githubusercontent.com/statgeek/7be124b98b37d51e7c002b85b6e9cf72/raw/ec0bc5cf8e1a3386c5c...";
%include tagset;

ods tagsets.sql file="H:\Desktop\FolderName/Sample1.txt";
proc print data=Sample1 ; run;
ods tagsets.sql close;

...and got this error message...
ERROR: Cannot load SSL support.
ERROR: Cannot open %INCLUDE file TAGSET.
ERROR: %INCLUDE has encountered an I/O/ error. Canceling submitted statements
ballardw
Super User

@d0816 wrote:
@ballardw

I ran this code...

filename tagset url "https://gist.githubusercontent.com/statgeek/7be124b98b37d51e7c002b85b6e9cf72/raw/ec0bc5cf8e1a3386c5c...";
%include tagset;

ods tagsets.sql file="H:\Desktop\FolderName/Sample1.txt";
proc print data=Sample1 ; run;
ods tagsets.sql close;

...and got this error message...
ERROR: Cannot load SSL support.
ERROR: Cannot open %INCLUDE file TAGSET.
ERROR: %INCLUDE has encountered an I/O/ error. Canceling submitted statements

I am not allowed to access Github so can't test. The errors appear to be related to the actual Github connection though. SSL would be, I believe, Secure Sockets Layer, which is in the internet communications magic. It may be that you are also blocked by the organization or need additional information in the filename to connect properly. If you can manualy download that file then you might try that and then use %INCLUDE using the full path and filename of the download.

 

Reeza
Super User
/*------------------------------------------------------------eric-*/
/*-- This tagset creates sql statements to create a table        --*/
/*-- and insert all the records in the dataset.  The resulting   --*/
/*-- output will have the table create statement followed by     --*/
/*-- the insert statements.                                      --*/
/*--                                                             --*/
/*-- This has only been tested with proc print, although it may  --*/
/*-- Work with other proc's as well.                             --*/
/*--                                                             --*/
/*-- This isn't anything fancy, all it handles are strings,      --*/
/*-- integers and numbers.  It could do more by using the        --*/
/*-- value of sasformat.                                         --*/
/*---------------------------------------------------------12Feb04-*/

proc template;
  define tagset tagsets.sql;

      /*---------------------------------------------------------------eric-*/
      /*-- Set up some look-up tables for convenience.                    --*/
      /*------------------------------------------------------------11Feb04-*/
      /* type translations */
      define event type_translations;
          set $types['string'] 'varchar';
          set $types['double'] 'float';
          set $types['int']    'integer';
      end;

      /* column name translation */
      define event name_translations;
          set $name_trans['desc'] 'description';
      end;
  
      define event initialize;
          trigger type_translations;
          trigger name_translations;

          /* types that need widths */
          set $types_with_widths['string'] "True";

          /* types that need quotes */
          set $types_with_quotes['string'] "True";
      end;
  
      /*---------------------------------------------------------------eric-*/
      /*-- Reset everything so we can run one proc print after another.   --*/
      /*------------------------------------------------------------11Feb04-*/
      define event table;
          unset $names;
          unset $col_types;
          unset $columns;
          unset $values;
          unset $lowname;
      end;


      define event colspec_entry;
          /*---------------------------------------------------------------eric-*/
          /*-- Ignore the obs column.  The value will get ignored because     --*/
          /*-- it will be in a header cell and we don't define a header       --*/
          /*-- event to catch it.                                             --*/
          /*------------------------------------------------------------12Feb04-*/
          break /if cmp(name, 'obs');

          /*---------------------------------------------------------------eric-*/
          /*-- Create a list of column names.  Translate the names            --*/
          /*-- if they are in the translate list.                             --*/
          /*------------------------------------------------------------11Feb04-*/
          set $lowname lowcase(name);
          do /if $name_trans[$lowname];      
              set $names[] $name_trans[$lowname];
          else;
              set $names[] $lowname;
          done;

          /* keep a list of types */
          set $col_types[] type;

          /* make a list of column type definitions */
          set $col_def $types[type];

          /* append width if needed */
          set $col_def $col_def "(" colwidth ")" /if $types_with_widths[type];
          
          set $columns[] $col_def;
      end;
      
      /*---------------------------------------------------------------eric-*/
      /*-- Catch the data label and get the data set name from it.        --*/
      /*------------------------------------------------------------11Feb04-*/
      define event output;
          start:
              set $table_name reverse(label);
              set $table_name scan($table_name, 1, '.');
              set $table_name reverse($table_name);
              set $table_name lowcase($table_name);
      end;    

     /*---------------------------------------------------------------eric-*/
     /*-- Print out the create table statement before Any data           --*/
     /*-- rows come along.                                               --*/
     /*------------------------------------------------------------11Feb04-*/
      define event table_body;
          put "Create table " $table_name "(";
          /* put "           "; */

          /* loop over the names, and column definitions */
          eval $i 1;
          unset $not_first;
          do /while $i <= $names;      
              /* comma's only after the first name */
              put ', ' /if $not_first;
              put $names[$i] " ";
              put $columns[$i];
              eval $i $i+1;
              set $not_first "True";
          done;    

          put ");" nl;
      end;
  
      /*---------------------------------------------------------------eric-*/
      /*-- Reset the values at the beginning of each row.  Print the      --*/
      /*-- insert statement at the end of each row.                       --*/
      /*------------------------------------------------------------11Feb04-*/
      define event row;
          start:
              unset $values;
          finish:
              trigger insert;
      end;

      /*---------------------------------------------------------------eric-*/
      /*-- Save away the data.  The Obs column won't hit this because     --*/
      /*-- it's a header.                                                 --*/
      /*------------------------------------------------------------12Feb04-*/
      define event data;
          do /if value;
              set $values[] strip(value);
          else;
              set $values[] ' ';
          done;
      end;
          
      /*---------------------------------------------------------------eric-*/
      /*-- Create the insert statement                                    --*/
      /*------------------------------------------------------------12Feb04-*/
      define event insert;
          finish:
              break /if ^$values;
          
              put "Insert into " $table_name;
              trigger print_names;
              put " Values";
              trigger print_values;
              put ";" nl;
      end;    
      
      /*---------------------------------------------------------------eric-*/
      /*-- Print the list of names.  This could use                       --*/
      /*-- a single putvars statement if it weren't for                   --*/
      /*-- the commas.                                                    --*/
      /*------------------------------------------------------------12Feb04-*/
      define event print_names;
          put "(";
          iterate $names;
          unset $not_first;
          do /while _value_;
              /* comma's only after the first name */
              put ", " /if $not_first;
              put lowcase(_value_);
              set $not_first "true";
              next $names;
          done;
          put ")";
      end;

      /*---------------------------------------------------------------eric-*/
      /*-- Print the values for the insert statement. Commas and quoting  --*/
      /*-- are an issue.  double up the quotes in strings.  Remove        --*/
      /*-- commas from numbers.                                           --*/
      /*------------------------------------------------------------12Feb04-*/
      define event print_values;
          put "(";

          eval $i 1;
          unset $not_first;

          iterate $values;

          do /while _value_;
              put ", " /if $not_first;

              do /if $types_with_quotes[$col_types[$i]]; 
                  put "'" ;
                  put tranwrd(_value_, "'", "''") /if ^cmp(_value_, ' ');
                  put "'";
              else;
                  do /if cmp(_value_, ' ');
                      put '0';
                  else;    
                      put tranwrd(_value_, "," , "") ;
                  done;
              done;    

              set $not_first "true";

              next $values;
              eval $i $i+1;
          done;

          put ")";
      end;
      
  end;
run;

Here's the code from the link. 

Reeza
Super User
  • Transpose your data to a long format instead of a wide format.
  • Extract the Quarter and Date from the variable name, which is now a column itself. You now have a long data set with time series type format. 
  • Clean up names using SCAN() function within a data step
  • Transpose to Wide again (if needed) using PROC TRANSPOSE and the ID statement to get the labels correct and use your new measure
  • OR calculate your new measures from the long format and append them to your data set and then Transpose. 

 

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/



@d0816 wrote:

@PaigeMiller 

yes you are right.  I gave you excel column names instead of sas column names. clearly I was not thinking.  

 

To give you the context, I have a dataset like below.

ID First Event Begin Quarter Last Event Begin QTR Last Event End QTR _01__Q3_1996 _02__Q4_1996 _03__Q1_1997 _04__Q2_1997 _05__Q3_1997 _06__Q4_1997 _07__Q1_1998 _08__Q2_1998 _09__Q3_1998 _10__Q4_1998 _11__Q1_1999 _12__Q2_1999 _13__Q3_1999 _14__Q4_1999 _15__Q1_2000 _16__Q2_2000 _17__Q3_2000 _18__Q4_2000 _19__Q1_2001 _20__Q2_2001 _21__Q3_2001 _22__Q4_2001 _23__Q1_2002 _24__Q2_2002 _25__Q3_2002 _26__Q4_2002 _27__Q1_2003 _28__Q2_2003 _29__Q3_2003 _30__Q4_2003 _31__Q1_2004 _32__Q2_2004 _33__Q3_2004 _34__Q4_2004 _35__Q1_2005 _36__Q2_2005 _37__Q3_2005 _38__Q4_2005 _39__Q1_2006 _40__Q2_2006 _41__Q3_2006 _42__Q4_2006 _43__Q1_2007 _44__Q2_2007 _45__Q3_2007 _46__Q4_2007 _47__Q1_2008 _48__Q2_2008 _49__Q3_2008 _50__Q4_2008 _51__Q1_2009 _52__Q2_2009 _53__Q3_2009 _54__Q4_2009 _55__Q1_2010 _56__Q2_2010 _57__Q3_2010 _58__Q4_2010 _59__Q1_2011 _60__Q2_2011 _61__Q3_2011 _62__Q4_2011 _63__Q1_2012 _64__Q2_2012 _65__Q3_2012 _66__Q4_2012 _67__Q1_2013 _68__Q2_2013 _69__Q3_2013 _70__Q4_2013 _71__Q1_2014 _72__Q2_2014 _73__Q3_2014 _74__Q4_2014 _75__Q1_2015 _76__Q2_2015 _77__Q3_2015 _78__Q4_2015 _79__Q1_2016 _80__Q2_2016 _82__Q4_2016 _83__Q1_2017 _84__Q2_2017 _85__Q3_2017 _86__Q4_2017 _87__Q1_2018 _88__Q3_2016 _89__Q2_2018 _90__Q3_2018 _91__Q4_2018 _92__Q1_2019 _93__Q2_2019
1 1998-Q1 2017-Q4 2018-Q2 $0.00 $220.00 $0.00 $0.00 $132.00 $138.00 $0.00 $487.00 $0.00 $0.00 $0.00 $0.00 $0.00 $919.00 $0.00 $0.00 $634.00 $557.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $739.00 $180.00 $0.00 $0.00 $529.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $33.00 $0.00 $5,577.00 $5,577.00 $5,467.00 $3,959.00 $3,698.00
2 2001-Q3 2017-Q4 2018-Q2 $0.00 $67.00 $150.00 $0.00 $0.00 $696.00 $0.00 $0.00 $1,007.00 $1,307.00 $2,824.00 $103.00 $1,918.00 $2,150.00 $1,229.00 $86.00 $0.00 $0.00 $1,179.00 $1,629.00 $2,679.00 $2,659.00 $551.00 $1,550.00 $3,552.00 $909.00 $894.00 $3,099.00 $2,087.00 $3,648.00 $878.00 $1,388.00 $2,450.00 $2,613.00 $694.00 $0.00 $0.00 $1,494.00 $3.00 $300.00 $1,088.00 $2,171.00 $688.00 $4,193.00 $3,856.00 $4,393.00 $4,744.00 $4,271.00 $3,654.00 $4,407.00 $4,468.00 $4,449.00 $1,849.00 $2,746.00 $2,852.00 $2,415.00 $1,956.00 $1,027.00 $0.00 $3,141.00 $3,987.00 $3,775.00 $0.00 $1,619.00 $3,053.00 $3,230.00 $2,009.00 $4,508.00 $6,575.00 $8,747.00 $6,005.00 $4,886.00 $4,382.00 $3,898.00 $3,481.00 $1,636.00 $2,343.00 $5,689.00 $3,519.00 $3,539.00 $4,791.00 $3,635.00 $3,968.00 $3,686.00 $0.00 $6,687.00 $3,877.00 $8,302.00 $8,302.00 $7,478.00 $15.00 $5,605.00
3 1996-Q1 2018-Q1 2018-Q2 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $534.00 $1,623.00 $0.00 $0.00 $0.00 $0.00 $121.00 $912.00 $0.00 $0.00 $392.00 $387.00 $0.00 $1,749.00 $312.00 $2,186.00 $2,211.00 $1,503.00 $0.00 $627.00 $2,013.00 $1,582.00 $1,146.00 $974.00 $2,438.00 $1,493.00 $3,083.00 $4,418.00 $2,753.00 $2,652.00 $4,118.00 $3,849.00 $2,573.00 $3,030.00 $3,291.00 $2,925.00 $3,519.00 $2,358.00 $2,340.00 $4,104.00 $3,618.00 $4,212.00 $1,112.00 $3,962.00 $3,031.00 $4,057.00 $361.00 $0.00 $0.00 $957.00 $3,360.00 $1,620.00 $0.00 $2,347.00 $3,332.00 $3,817.00 $553.00 $478.00 $2,929.00 $3,419.00 $2,310.00 $2,671.00 $2,866.00 $3,456.00 $3,224.00 $2,951.00 $2,927.00 $2,566.00 $0.00 $0.00 $3,241.00 $0.00 $2,228.00 $2,956.00 $3,619.00 $3,619.00 $3,788.00 $2,962.00 $4,186.00

 

I need to add three columns like below where I need to match the quarter with the column quarter and pick that value in that matching column quarter like below. If there is no matching column quarter I need to identify that and so put -$1.00 (no value is actual dataset is negative). I thought I will start with making the column names same but clearly I was not thinking.

Amount in First Begin QTR Amount in Last Begin QTR Amount in Last End QTR
$0.00 $0.00 $5,577.00
$2,679.00 $0.00 $8,302.00
-$1.00 $2,228.00 $3,619.00

 

How can I add these three columns?

 

Thank you.


 

gamotte
Rhodochrosite | Level 12

Edit: Removed unecessary grouping in the regexp

Those are not valid sas columns names. Since you are renaming them, for future purpose, you'd better chose

valid names.

 

option validvarname=any;

data have;
    input "79. Q1 2016"n "80. Q2 2016"n;
    cards;
1 2
;
run;

data _NULL_;
    call execute('data want; set have; rename');

    do until (fend);
        set sashelp.vcolumn end=fend;
        where libname="WORK" and memname="HAVE";
        call execute(cats('"',NAME,'"n=',prxchange("s/^[0-9]+. (Q[0-9]+) /$1_/",-1,NAME)));
    end;
    call execute('; run;'); 
    stop;
run;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 12 replies
  • 1557 views
  • 0 likes
  • 5 in conversation