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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.