Reordering columns from a data set

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Reordering columns from a data set

I have a data set with 35 columns. Some of the columns are dates that get updated monthly and have the prefix of AvgBal. Below is a small sample of a few column headers.

CustomerName | Rank | Change_Since_Prior_QTR | AvgBal_2012-11-30.......AvgBal_2013-11-30 | CustIDKey |

The AvgBal columns change monthly and report a trailing 13 month period so the reporting range next month will be AvgBal_2012-12-31 - AvgBal_2013-12-31. What I want to do is create a retain function so that I can arrange the other columns in my data set since a majority of them are out of order.

This is what I have tried so far:

proc sql noprint; select Date into :LastMonth from Home.DateTable where Row = 1;

%let LastMonth = &LastMonth.; /*Creating a macro for the last month*/

proc sql noprint; select Date into :FirstMonth from Home.DateTable where Row = 13;

%let FirstMonth = &FirstMonth.;/*Creating a macro for the first month*/

data Export;

  retain Rank CustomerName CustIDKey AvgBal_&FirstMonth. - AvgBal_&LastMonth. Change_Since_Prior_QTR;

  set Home.Final_&YearQTR.; run;

I get the following error message:

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 76-322: Syntax error, statement will be ignored.


I have also tried a like statement - 'AvgBal_%' but this didn't work either.


Any other suggestions would be welcomed.


Accepted Solutions
Solution
‎01-26-2014 09:10 AM
Respected Advisor
Posts: 3,799

Re: Reordering columns from a data set

Posted in reply to RichardinOz

This might work.

data Export;

  retain Rank CustomerName CustIDKey;

  if 0 then set home.final_&yearQTR(keep=AvgBalSmiley Happy;

  retain Change_Since_Prior_QTR;

  set Home.Final_&YearQTR.;

run;

View solution in original post


All Replies
PROC Star
Posts: 1,325

Re: Reordering columns from a data set

Posted in reply to SasGuy614

Hi,

Is it really AvgBal_2012-11-30 ?  I don't think that's a valid variable name in SAS.  Maybe it is if you use a name literal, "AvgBal_2012-11-30"n ?

That issue aside, you can use var1-varN for variable lists with numeric suffixes (x1-x5, y2-y100, etc).  But It doesn't know about date suffixes. 

You could do something like (untested):

proc contents data=mydata (keep=AvgBalSmiley Happy out=AvgBalVarList;

run;

* Make macro var &AvgBalVarList which is a list of all the AvgBal: variables, in alphabetical (chronological) order;

proc sql;

  select name from AvgBalVarList 

    into :AvgBalVarList separated by " "

    order by name

  ;

quit;

  

*Check the macro var;

%put &AvgBalVarList ;

data want;

set have;

retain Rank CustomerName CustIDKey &AvgBalVarList Change_Since_PriorQtr ;

run;

Something like that should do it.  That said, typically this kind of data structure, where you have information (yyyymmdd) as part of variable names causes lots of headaches.  Coding life is usually much easier if you transpose it to a more normalized format, something with variables like:

Rank CustomerName CustIDKey Month AvgBal

So you end up with fewer variables, more records (one record for each customer-month), and it's much easier to compute moving averages, subset by date, look for change from month to month, etc, without mucking about with lots of array coding, or macros that have to compute the date suffix for a variable names.  If I catch myself putting data in a variable name, it's usually a warning sign.

HTH,

-Q.

Super Contributor
Posts: 644

Re: Reordering columns from a data set

Posted in reply to SasGuy614

The knid of table structure you are dealing with is "accountant style spreadsheet think" used by accountants to scan changes along a row.  I endorse Quentin's view that the best layout for a database would involve transposing the AvgBal information from rows to a single column.  However, assuming your data at least has the avgbal columns in the right order you might like to try using the : notation  (no need for macro variables)

data Export;

  retain Rank CustomerName CustIDKey AvgBal: Change_Since_Prior_QTR;

  set Home.Final_&YearQTR.;

run;

Richard

Super User
Super User
Posts: 7,079

Re: Reordering columns from a data set

Posted in reply to RichardinOz

That will not work because the AVGBAL.... variables are not defined at the time that the RETAIN statement is compiled.

Solution
‎01-26-2014 09:10 AM
Respected Advisor
Posts: 3,799

Re: Reordering columns from a data set

Posted in reply to RichardinOz

This might work.

data Export;

  retain Rank CustomerName CustIDKey;

  if 0 then set home.final_&yearQTR(keep=AvgBalSmiley Happy;

  retain Change_Since_Prior_QTR;

  set Home.Final_&YearQTR.;

run;

Contributor
Posts: 29

Re: Reordering columns from a data set

Posted in reply to data_null__

When I run this I get the following error message: ERROR: The variable 'AvgBal:'n in the DROP, KEEP, or RENAME list has never been referenced.

I just posted a few minutes ago that the AvgBal columns are numeric. Not sure if that makes a difference or not.

Thanks for the suggestion

Super User
Super User
Posts: 7,079

Re: Reordering columns from a data set

Posted in reply to SasGuy614

Error message is saying that there are no variables in your input dataset whose names begin with the letters AVGBAL.

Are you sure these variables exist?

Contributor
Posts: 29

Re: Reordering columns from a data set

Posted in reply to data_null__

Correction...this did work. I had the wrong table reference in the two set statements. Can you explain further what the "If 0 then set" piece means? I'm rather new to using SAS so using multiple retain statements didn't even cross my mind.

Thanks again!

Contributor
Posts: 29

Re: Reordering columns from a data set

Posted in reply to SasGuy614

These have all been good suggestions. I couldn't get the first few to work so I changed the field from character to numeric for my AvgBal column. The columns now look like this: AvgBal201212 AvgBal 201301 AvgBal201302 ..........I was going out on a limb thinking that it would be easier to manipulate the data if it was numeric but I could be wrong Smiley Happy.

Super User
Super User
Posts: 7,079

Re: Reordering columns from a data set

Posted in reply to SasGuy614

Do the variables already exist on the input dataset?  Because variables names are not normally allowed to have hyphens in them.

Also do all the variables you want to create exist on the input dataset? Or are you trying to generate a list that will also created new variables to fill in gaps in the sequence of variable names?

The most serious problem with the wa you are trying to do this is that you cannot use a variable list (A1-A5 or A--Z or ASmiley Happy for variables that do not exist. So you cannot use a variable list to do what you want because you need to put the RETAIN statement before the variables are defined to change the order.

You will instead need to either use the trick from data_null_ using the KEEP= dataset option or generate a list of names.

If the variables already exist then pull all of the names from SAS metadata.

proc sql noprint ;

   select name into :names separated by ' '

   from dictionary.columns

   where libname='HOME' and memname="FINAL_&YearQTR"

       and upcase(name) like 'AVGBAL%'

   order by input(substr(name,7),yymm6.)

  ;

quit;

If the names to not exist then generate the names using a data step;

data _null_;

   length names $1000;

   do date=&firstmonth to &lastmonth;

     names=catx(' ',lastmonth,cats('AvgBal',put(date,yymmn6.)));

   end;

   call symputx('names',names);

run;

data Export;

  retain Rank CustomerName CustIDKey &names Change_Since_Prior_QTR;

  set Home.Final_&YearQTR.;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 661 views
  • 3 likes
  • 5 in conversation