Hi,
I have a dataset that is at the person/event level--each row represents one event for one person. It has ID variable, a target date, a variable I created to serve as the column heads after transposing, a month count, and a count variable. The month count indicates the number of months either before (in which case it will be negative) or after (positive) the target date that an event occurred. The variable I created corresponds with the month count variable:
ID Target_date Mth_count Transpose_var Count
1 01/2010 -5 5_pre 5
1 01/2010 -3 3_pre 7
1 01/2010 2 2_post 13
1 01/2010 7 7_post 27
I have transposed the data such that the month counts are now columns (I kept the target date):
proc transpose data=have out=want prefix=mth_;
by ID target_date;
var count;
id transpose_var;
run;
This is the resulting dataset:
ID Target_date mth_5_pre mth_3_pre mth_2_post mth_7_post
1 01/2010 5 7 13 27
The problem is that I need all the resulting month columns to be in order (ie, mth_10_pre, mth_9_pre...mth_0, mth_1_post, mth_2_post...mth_10_post). I know that the order is off because Person 1 has missing values for the other months, but I'm hoping there's a way to get SAS to output the columns in the order I want them.
Any help is much appreciated.
Try Art's macro:
A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity
Create some observations with ID=missing that establish the desired variable order, place these records at the beginning of the data, (perhaps using a view). Then remove that ID with a WHERE data set option on the OUT= data specification. Easy.
Message was edited by: data _null_
SQL reorder variables' name .
data have;
input ID Target_date $ Mth_count Transpose_var $ Count;
cards;
1 01/2010 -5 5_pre 5
1 01/2010 -3 3_pre 7
1 01/2010 2 2_post 13
1 01/2010 7 7_post 27
;;;;
run;
proc transpose data=have out=temp prefix=mth_;
by ID target_date;
var count;
id transpose_var;
run;
proc sql;
select name into : list separated by ' '
from dictionary.columns
where libname='WORK' and memname='TEMP' and name like 'mth%'
order by input(compress(name,,'kd'),best32.);
quit;
data want;
retain ID target_date &list;
set temp;
run;
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.