topic Re: rename column in SAS Programming

rename column

d0816 — Thu, 05 Mar 2020 14:23:26 GMT

Hi,

I need help renaming column names.

I have around 100 columns like this...

79. Q1 2016

80. Q2 2016

88. Q3 2016

82. Q4 2016

83. Q1 2017

84. Q2 2017

85. Q3 2017

86. Q4 2017

87. Q1 2018

89. Q2 2018

90. Q3 2018

91. Q4 2018

..which I want to change to..

2016-Q1

2016-Q2

2016-Q3

2016-Q4

2017-Q1

2017-Q2

2017-Q3

2017-Q4

2018-Q1

2018-Q2

2018-Q3

2018-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.

Re: rename column

PaigeMiller — Thu, 05 Mar 2020 14:37:52 GMT

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.

Re: rename column

gamotte — Thu, 05 Mar 2020 14:52:03 GMT

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;

Re: rename column

d0816 — Thu, 05 Mar 2020 15:06:36 GMT

@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.

Re: rename column

PaigeMiller — Thu, 05 Mar 2020 15:37:35 GMT

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?

Re: rename column

d0816 — Thu, 05 Mar 2020 15:29:22 GMT

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.

Re: rename column

PaigeMiller — Thu, 05 Mar 2020 15:42:37 GMT

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

Re: rename column

d0816 — Thu, 05 Mar 2020 16:25:30 GMT

@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.

Re: rename column

Reeza — Thu, 05 Mar 2020 17:52:18 GMT

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.

Re: rename column

ballardw — Thu, 05 Mar 2020 21:09:18 GMT

@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.

Re: rename column

d0816 — Fri, 06 Mar 2020 15:17:14 GMT

@ballardw

I ran this code...

filename tagset url "https://gist.githubusercontent.com/statgeek/7be124b98b37d51e7c002b85b6e9cf72/raw/ec0bc5cf8e1a3386c5cf71d2280b242b7f87dadd/SAS_tagsets_sql_reprex.sas";
%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

Re: rename column

ballardw — Fri, 06 Mar 2020 17:00:44 GMT

@d0816 wrote:
@ballardw

I ran this code...

filename tagset url "https://gist.githubusercontent.com/statgeek/7be124b98b37d51e7c002b85b6e9cf72/raw/ec0bc5cf8e1a3386c5cf71d2280b242b7f87dadd/SAS_tagsets_sql_reprex.sas";
%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.

Re: rename column

Reeza — Fri, 06 Mar 2020 17:40:02 GMT

/*------------------------------------------------------------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.