How can i transpose 2 variable in one go?

[StickyRoll]

How can i transpose 2 variable in one go?

I have a table that looks like this:

data testdata;

office='LA';

dept='IT';

year=2024;

seq='1';

code='101';

me_amt=10000;

me_pct=0;

output;

office='LA';

dept='IT';

year=2024;

seq='1';

code='102';

me_amt=66;

me_pct=12;

output;

office='MI';

dept='FIN';

year=2024;

seq='1';

code='101';

me_amt=333;

me_pct=1;

output;

office='MI';

dept='FIN';

year=2024;

seq='1';

code='102';

me_amt=98.7;

me_pct=12.3;

output;
run;

What i want to achieve is something like this:

data want;

office='LA';

dept='IT';

year=2024;

seq='1';

amtAAA=10000;

pctAAA=0;

amtBBB=66;

pctBBB=12;

output;

office='MI';

dept='FIN';

year=2024;

seq='1';

amtAAA=333;

pctAAA=1;

amtBBB=98.7;

pctBBB=12.3;

output;

run;

I want to make it a single row for each unique office + dept + year + seq.

I can think of the way of achieving this for only "me_amt" is by first hardcoding a new column (flag) with the name "amtAAA".
Then, sort it with proc sort by office + dept + year + seq.

Then, transpose it with a proc transpose statement with :
id flag;
var me_amt;

However, how do i achieve this by including "me_pct" as well? as my transpose statement will only work for transposing "me_amt".

[Patrick]

I didn't really understand where you're taking naming components AAA and BBB from so I've used variable code instead. Does below return what you're after?

data have;
    input office $ dept $ year seq $ code $ me_amt me_pct;
    datalines;
LA IT 2024 1 101 10000 0
LA IT 2024 1 102 66 12
MI FIN 2024 1 101 333 1
MI FIN 2024 1 102 98.7 12.3
;
run;

proc sql noprint;
  select 
    distinct 
    cats('me_amt_',code), cats('me_pct_',code)
      into :me_amt separated by ' ', :me_pct separated by ' '
  from have
  ;
quit;

proc sort data=have out=want;
  by office dept year seq;
run;

data want(drop=_: me_amt me_pct);
  set want;
  by office dept year seq;
  array a_me_amt{*} &me_amt;
  array a_me_pct{*} &me_pct;
  retain a_me_amt a_me_pct;
  do _i=1 to dim(a_me_amt);
    if scan(vname(a_me_amt[_i]),-1,'_')=code then
      do;
        a_me_amt[_i]=me_amt;
        a_me_pct[_i]=me_pct;       
      end;
  end;
  if last.office then 
    do;
      output;
      call missing(of _all_);
    end;
run;

proc print data=want;
run;

[quickbluefish]

I think you'd just want the above to be:
if last.seq then...
...instead of:
if last.office then...

[PaigeMiller]

When I read the original question, and then see the difficult programming that @Patrick provides, I feel compelled to ask @StickyRoll  why this data set needs to be transposed in the first place. What is the benefit of re-arranging the data in this fashion? It seems to me that re-arranging the data set just makes programming the next step(s) much more difficult. If the goal is to create a wide table for output, then no transpose is needed, and PROC REPORT will do this without the difficulties of transposing.

[Stu_SAS]

Hey @StickyRoll! While the below solution is two steps, it does prevent you needing to sort and merge and stays entirely within PROC TRANSPOE. For this example, you can take advantage of the copy statement in PROC TRANSPOSE to bring the variables you need forward. We'll use the variable code to keep track of each variable's row position and add a prefix for its name.

proc transpose data=testdata 
               out=testdata2 
               prefix=me_amt_;
    by office dept year seq;
    id code;
    copy me_pct code;
    var me_amt;
run;

proc transpose data=testdata2 
               out=want(drop=_NAME_) 
               prefix=me_pct_;
    by office dept year seq;
    id code;
    copy me_amt:;
    var me_pct;
run;

Let's break this down. We'll start by looking at the first step:

office	dept	year	seq	me_pct	code	_NAME_	me_amt_101	me_amt_102
LA	IT	2024	1	0	101	me_amt	10000	        66
LA	IT	2024	1	12	102		.	        .
MI	FI	2024	1	1	101	me_amt	333	        98.7
MI	FI	2024	1	12.3	102		.	        .

Notice we have me_amt in the format we want. Now we just need to get me_pct. We copied forward the values of me_pct and code which we are using as our ID. For the final step we just need to do the opposite: we will transpose by office dept year seq, get our ID from code, then copy forward all the me_amt variables.  This results in the final dataset that we want.

office	dept	year	seq	me_amt_101	me_amt_102	me_pct_101	me_pct_102
LA	IT	2024	1	10000	        66	        0	        12
MI	FI	2024	1	333	        98.7	        1	        12.3

[yabwon]

Just for fun. Two steps with some macros, for lazy-typers 🙂

%macro tr(var);
proc transpose data=testdata out=&var.(drop=_name_) prefix=&var._;
by office dept year seq;
id code;
var &var.;
run;
%mend tr;


%tr(me_amt);
%tr(me_pct);

data want_12;
  merge me_:;
  by office dept year seq;
run;
proc print;
run;

Bart

[Tom]

You can do this with two proc transpose steps.  The first one converts your original "middle" format dataset into an actual "tall" format dataset.  Then the second one converts to a "wide" format dataset.

First let's convert your wallpaper code for making the dataset into a simple data step that reads from in-line data.  Notice how the code is not only easier to write but the data it represents is much clearer.

data have;
  input office $ dept $ year seq $ code $ me_amt me_pct;
datalines;
LA IT  2024 1 101 10000  0
LA IT  2024 1 102    66 12
MI FIN 2024 1 101   333  1
MI FIN 2024 1 102  98.7 12.3
;

Now let's run the two transpose steps.

proc transpose data=have out=tall;
  by office dept year seq code;
  var me_amt me_pct;
run;

proc transpose data=tall out=want(drop=_name_) delim=_ ;
  by office dept year seq ;
  id _name_ code ;
  var col1 ;
run;

Results

This method will work for almost any number of variables, as long as they are all of the same type.

[Ksharp]

Check the MERGE skill proposed by Me and Arthur.T :

https://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;
    input office $ dept $ year seq $ code $ me_amt me_pct;
    datalines;
LA IT 2024 1 101 10000 0
LA IT 2024 1 102 66 12
MI FIN 2024 1 101 333 1
MI FIN 2024 1 102 98.7 12.3
;
run;
proc sql noprint;
select distinct catt('have(where=(code="',code,'" ) 
 rename=(me_amt=me_amt_',code,' me_pct=me_pct_',code,'))') into :merge separated by ' '
 from have;
quit;
data want;
merge &merge.;
by office dept year;
drop code;
run;