proc transpose dataset

[cw2024]

I've never done a proc tranpose and cannot get the desired result after reading the documentation

I have a dataset that looks like this

Acct1   AcctLevel   State   Last_State_Date

1             A                   OH      05-23-2023

1             A                   MI       11-30-2023

2            C                   TX       01-22-2023

2            C                   CA       04-14-2023

2            C                   MO      07-25-2023

3            F                    OH      06-22-2023

3            F                     IN        12-12-2023

3            K                     WA      08-01-2023

And the desired output is:

Acct      AcctLevel      State1    Date1                    State2       Date2                  State3       Date 3

1                 A                   OH         05-23-2023        MI               11-30-2023

2                C                    TX          01-22-2023        CA              04-14-2023        MO            07-25-2023

3                F                    OH          06-22-2023       IN               12-12-2023

3                K                    WA         08-01-2023

Please help

[ballardw]

Best is to provide example data in the form of a working data step pasted into a text box opened on the forum with the </> icon above the main message window.

The data step is important to provide something we can test code with and know actual properties of your variables. For example SAS date values are numeric but when you post something like  05-23-2023 we cannot tell if this is a SAS date value with the MMDDYY or similar format applied or a character value.

This gets to be fairly important as Transpose doesn't play well with numeric and character values in some forms of transpose.

One thing to consider is what exactly will be done with the output set? As in, describe what can be done with that set that can't be done with the current structure as the "wide" format with repeated variables like your State and Date are typically much harder to use.

These creates data set in the general idea you have provided. Note the data step to provide working data.

data have;
   input Acct1 AcctLevel $ State $ Last_State_Date :mmddyy10.;
   format last_state_date mmddyy10.;
datalines;
1  A   OH 05-23-2023
1  A   MI 11-30-2023
2  C   TX 01-22-2023
2  C   CA 04-14-2023
2  C   MO 07-25-2023
3  F   OH 06-22-2023
3  F   IN 12-12-2023
3  K   WA 08-01-2023
run;

proc transpose data=have out=transstate (drop=_name_)
     prefix=State;
by acct1 Acctlevel;
var state;
run;
proc transpose data=have out=transdate (drop=_name_)
     prefix=Date;
by acct1 Acctlevel;
var last_state_date;
run;

data want;
   merge transstate transdate;
   by  acct1 Acctlevel;
run;

If you really need column order (SAS doesn't care for almost any purpose what the order of variables may be) then search the forum for the many threads related to variable order.

[Ksharp · Accepted solution]

data have;
   input Acct1 AcctLevel $ State $ Last_State_Date :mmddyy10.;
   format last_state_date mmddyy10.;
datalines;
1  A   OH 05-23-2023
1  A   MI 11-30-2023
2  C   TX 01-22-2023
2  C   CA 04-14-2023
2  C   MO 07-25-2023
3  F   OH 06-22-2023
3  F   IN 12-12-2023
3  K   WA 08-01-2023
;

data temp;
 set have;
 by Acct1 AcctLevel;
 if first.AcctLevel then n=0;
 n+1;
run;
proc sql noprint;
select distinct catt('temp(where=(n=',n,') rename=(State=State',n,' Last_State_Date=Date',n,'))') 
       into :merge separated by ' '
  from temp;
quit;
data want;
 merge &merge.;
 by Acct1 AcctLevel;
 drop n;
run;

[cw2024]

I apologize for not including workable data via cards, and not being clear about formats/informats of my variables.  It was my first post, thank you so much both of you that replied!