BookmarkSubscribeRSS Feed
Walternate
Obsidian | Level 7

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.

3 REPLIES 3
data_null__
Jade | Level 19

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.

proc format;
  
picture mth
      -
10--1='009_pre'
     
0='009'
     
1-10='009_post'
   ;
   run;
data mth;
   input ID  Target_date $  Mth count;
   cards;
1        01/2010   -5   5
1        01/2010   -3   7
1        01/2010    2   13
1        01/2010    7   27
;;;;
   run;
proc print;
  
run;
data mthv / view=mthv;
   if 0 then set mth;
   if _n_ eq 1 then do mth=-10 to 10;
     
output;
     
end;
  
set mth;
   output;
  
run;
proc transpose data=mthv out=want(drop=_name_ where=(not missing(id))) prefix=mth_;
   by ID target_date;
   var count;
   id mth;
   format mth mth.;
  
run;
proc print;
  
run;

Capture.PNG

Message was edited by: data _null_

Ksharp
Super User

SQL reorder variables' name .

Code: Program

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;

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
  • 3 replies
  • 2458 views
  • 0 likes
  • 4 in conversation