I am using the below procs which give me close to the desired result. The first ID does not have the full range of dates I am displaying so the final transpose doesn't show it in order. It starts with the first date that the first ID has. I tried to work around moving the ID's around but I get an error because I have the ID in the by statement. Is there any work around for this? I'm going to end up cutting/pasting cells in excel which I'm not a fan of. What I have is a column starting with 01/31/2014cure_count, when the range of dates starts with 08/31/2013. I would like to have the range of dates in order regardless of the ID Any ideas will be appreciated.
proc transpose data=r4_display_prep out=r4_test_transpose(drop=_label_);by power_id record_date;
var cure_count PD_CD max_pass_date;run;
proc transpose data=r4_test_transpose out=r4_final_transpose(drop=_name_);by power_id;id record_date _name_;var col1;run;
Thanks,
Mark
Hi,
You will find many posts on this forum which deal with this concept of putting date information as columns headers. I would strongly advise against such a thing. The reason is that you will lose a lot of functionality built into SAS, and there are other ways to get what you want. Now, what I would do is this:
Have a datastep which goes through your data and assigns a column number to the dates, i.e. in the order they should appear in the output. Then transpose or alternatively array process your data to get transposed table, and assign the LABEL of the variable to be the date in question. Hence you would end up with a dataset with structure:
...
COL1 label="01Mar2014"
COL2 label="02Mar2014"
...
This way you can refer to the columns using COLx, e.g. col1-colx. You then have full array processing functionality, plus the information in the label can be exported to Excel in the correct order. Try to avoid putting data elements as column descriptors, i.e. separate table metadata from your data.
I am not sure if you can do it in one-step Proc Transpose, but for this kind of 'variable order' rearrangement, you can reliably do it using Retain statement by an additional date step.
data new_order;
retain var1 var2.....varN /*this is the order you want to see*/;
set old;
run;
Good luck,
Haikuo
Can you provide us a sample data which you are trying to transpose, then i can able to check the code.
Hi,
You will find many posts on this forum which deal with this concept of putting date information as columns headers. I would strongly advise against such a thing. The reason is that you will lose a lot of functionality built into SAS, and there are other ways to get what you want. Now, what I would do is this:
Have a datastep which goes through your data and assigns a column number to the dates, i.e. in the order they should appear in the output. Then transpose or alternatively array process your data to get transposed table, and assign the LABEL of the variable to be the date in question. Hence you would end up with a dataset with structure:
...
COL1 label="01Mar2014"
COL2 label="02Mar2014"
...
This way you can refer to the columns using COLx, e.g. col1-colx. You then have full array processing functionality, plus the information in the label can be exported to Excel in the correct order. Try to avoid putting data elements as column descriptors, i.e. separate table metadata from your data.
You can add an extra set of records with a dummy value for POWER_ID and all values of the date variable and then delete it from the resulting data set.
Note that you should use YYMMDD format instead of MMDDYY or DDMMYY for your date strings if you want them to sort more naturally as labels or variable names.
proc sort data=r4_display_prep(keep=record_date) nodupkey out=dummy ;
by record_date ;
run;
data dummy2 ;
set dummy r4_display_prep ;
run;
proc transpose data=dummy2 out=r4_test_transpose;
by power_id record_date;
var cure_count PD_CD max_pass_date;
run;
proc transpose data=r4_test_transpose
out=r4_final_transpose(drop=_name_ where=(not missing(power_id));
by power_id;
id record_date _name_;
var col1;
run;
Tom wrote:
You can add an extra set of records with a dummy value for POWER_ID and all values of the date variable and then delete it from the resulting data set.
Note that you should use YYMMDD format instead of MMDDYY or DDMMYY for your date strings if you want them to sort more naturally as labels or variable names.
proc sort data=r4_display_prep(keep=record_date) nodupkey out=dummy ;
by record_date ;
run;
data dummy2 ;
set dummy r4_display_prep ;
run;
proc transpose data=dummy2 out=r4_test_transpose;
by power_id record_date;
var cure_count PD_CD max_pass_date;
run;
proc transpose data=r4_test_transpose
out=r4_final_transpose(drop=_name_ where=(not missing(power_id));
by power_id;
id record_date _name_;
var col1;
run;
This is the correct correct answer.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: