topic Re: proc transpose dataset in SAS Procedures

proc transpose dataset

cw2024 — Wed, 24 Jan 2024 22:11:53 GMT

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

Re: proc transpose dataset

ballardw — Thu, 25 Jan 2024 00:10:20 GMT

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.

Re: proc transpose dataset

Ksharp — Thu, 25 Jan 2024 03:00:55 GMT

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;

Re: proc transpose dataset

cw2024 — Thu, 25 Jan 2024 15:32:09 GMT

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!