Help using Base SAS procedures

Transposing but with columns in correct order

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

Transposing but with columns in correct order

I am trying to transpose a data set with the column names as dates to later use in iml matrix multiplication (so thus, the columns must be sorted correctly)

proc sort data=msfinal2;

by cluster venue game_date;

proc transpose data=msfinal2 out=test1234 LET;

by cluster venue;

id game_date;

var finalvalue1;

run;

And from this the columns are out of order, easy to correct in JMP, but I need this done automatically. Ive tried other suggestins ive found through google but havent gotten it to work. Any suggestions?


Accepted Solutions
Solution
‎02-19-2013 10:46 PM
PROC Star
Posts: 7,468

Re: Transposing but with columns in correct order

If they are the only variables that start with an underscore you could use something like:

data have;

  input x _08_09_2012 _04_05_2012 _07_09_2012;

  cards;

A    3 1 2

;

proc sql noprint;

  select name

    into :names separated by " "

      from dictionary.columns

        where libname="WORK" and

              memname="HAVE" and

              substr(name,1,1) eq "_"

          order by name

  ;

quit;

data have;

  retain x &names.;

  set have;

run;

View solution in original post


All Replies
PROC Star
Posts: 7,468

Re: Transposing but with columns in correct order

Add a datastep, at the end, using a retain statement (before the set statement) to put the variables in the order you want them to be.

Frequent Contributor
Posts: 82

Re: Transposing but with columns in correct order

well that work if there are about 100 variables, named _04_05_2012 through _08_09_2012 for example? Can I do a retain with a range?

Solution
‎02-19-2013 10:46 PM
PROC Star
Posts: 7,468

Re: Transposing but with columns in correct order

If they are the only variables that start with an underscore you could use something like:

data have;

  input x _08_09_2012 _04_05_2012 _07_09_2012;

  cards;

A    3 1 2

;

proc sql noprint;

  select name

    into :names separated by " "

      from dictionary.columns

        where libname="WORK" and

              memname="HAVE" and

              substr(name,1,1) eq "_"

          order by name

  ;

quit;

data have;

  retain x &names.;

  set have;

run;

Frequent Contributor
Posts: 82

Re: Transposing but with columns in correct order

Yea,  I tried something like that, the sql says no rows were selected. Should I have to change anything besides the memname (and the x statement from retain, and the dataset name)

I guess I am not seeing what the name statement should be

PROC Star
Posts: 7,468

Re: Transposing but with columns in correct order

Did you make sure that the libname and memname were in UPPERCASE?

Super User
Super User
Posts: 7,039

Re: Transposing but with columns in correct order

It is probably easiest to change your naming convention so that you put Year first and Day last. Then normal alphanumeric sorting will sort in ascending date order.

The other problem with PROC TRANSPOSE is that it creates the variables as it sees them. If you have holes in your data then variables can be created in the wrong order.  One trick to fix this is prefix a set of rows that have all possible values of GAME_DATE in the right order by missing values for the BY variables.  You can then drop the extra record from the output dataset.

Frequent Contributor
Posts: 82

Re: Transposing but with columns in correct order

Changing the date format didnt really change how proc format outputted (not sure if it was supposed to), and to be honest I have no idea how to that second part. I dont know what you mean a row of dates in right order by missing values. I can create a row with the date values, but not sure what you are saying in full context

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 277 views
  • 3 likes
  • 3 in conversation