BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Xamius32
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

7 REPLIES 7
art297
Opal | Level 21

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.

Xamius32
Calcite | Level 5

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?

art297
Opal | Level 21

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;

Xamius32
Calcite | Level 5

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

art297
Opal | Level 21

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

Tom
Super User Tom
Super User

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.

Xamius32
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 1582 views
  • 3 likes
  • 3 in conversation