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?
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;
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.
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?
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;
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
Did you make sure that the libname and memname were in UPPERCASE?
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.