DATA Step, Macro, Functions and more

How do I accomplish a dynamic "transpose" with multiple variables?

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

How do I accomplish a dynamic "transpose" with multiple variables?

I put the word transpose in quotes in the title because I'm not totally sure that that is the 100% correct term for what I'm trying to accomplish.  Most of the (large number) of results for my searches on the subject are performing the transpose in such a way that part of the data becomes the output dataset header, which is not my goal.

 

Essentially, what I want to achieve is to take the input dataset :

 

strUniqID intNonUniqID strAddress dteAppDate intAppNum
A12345 1 123 45th St 1/2/1970 47
A12345 2 234 56th St 3/4/1978 39
B12345 3 345 67th St 8/6/1982 35
B12345 4 456 78th St 12/31/1994 22

 

and manipulate it so that the output looks like :

 

strUniqID intNonUniqID_1 strAddress_1 dteAppDate_1 intAppNum_1 intNonUniqID_2 strAddress_2 dteAppDate_2 intAppNum_2
A12345 1 123 45th St 1/2/1970 47 2 234 56th St 3/4/1978 39
B12345 3 345 67th St 8/6/1982 35 4 456 78th St 12/31/1994 22

 

I don't know if proc transpose can accomplish this and all of the macro examples I've seen out there don't quite fit what I'm trying to do.  Any input would be MOST appreciated!!

 

Many thanks!!


Accepted Solutions
Solution
‎11-20-2017 09:34 AM
Super User
Posts: 10,850

Re: How do I accomplish a dynamic "transpose" with multiple variables?

Check the MERGE skill proposed by me,Arthur.T,Matt.

 

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

 

 

 

data have;
  infile cards expandtabs truncover;
 
  input strUniqID $	intNonUniqID	strAddress & $20.	dteAppDate : mmddyy10.	intAppNum;
format dteAppDate  mmddyy10.;
cards;
A12345	1	123 45th St	 1/2/1970	47
A12345	2	234 56th St	 3/4/1978	39
B12345	3	345 67th St	 8/6/1982	35
B12345	4	456 78th St	 12/31/1994	22
;

data have;
 set have;
 by strUniqID;
 if first.strUniqID then n=0;
 n+1;
run;

proc sql noprint;
select distinct catt('have(where=(n=',n,') 
 rename=(intNonUniqID=intNonUniqID_',n,'
        strAddress=strAddress_',n,'
        dteAppDate=dteAppDate_',n,'
        intAppNum=intAppNum_',n,'))')  into : list separated by ' '
 from have;
 quit;

 %put list ;

 data want; 
  merge &list ;
  by strUniqID;
  drop n;
run;

View solution in original post


All Replies
Super User
Posts: 24,012

Re: How do I accomplish a dynamic "transpose" with multiple variables?

I don't think you can do this in a single PROC TRANSPOSE, you can do multiple and merge the results together. 

 

Example 2 here using PROC TRANSPOSE:

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

 

Example 2 here using DATA STEP + ARRAY:

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

 

Or you can use a user written macro here:

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

 

 

Contributor
Posts: 20

Re: How do I accomplish a dynamic "transpose" with multiple variables?

See, those links are exactly what I was talking about when I was saying that all of my searches weren't demonstrating what I wanted to do.  All of those links, which incidentally, I'd already reviewed, transpose the data such that one of the variables from the original dataset ends up as part of the output dataset header.  I don't want that at all.  I want to keep the original header test and just append the iteration number.  For instance, if my headers were ID,A,B,C,D,etc... I'd be looking to have that become ID,A_1,B_1,C_1,D_1,A_2,B_2,C_2,D_2,etc...

 

If I am not being too clear, I apologize.  I am choosing my words as best I can.

Super User
Posts: 24,012

Re: How do I accomplish a dynamic "transpose" with multiple variables?

You can have two variables in your ID statement, in your case, 

strUniqID intNonUniqID

 

 

If these are not working for you, pick an approach you'd like to go with, post what you've tried and we can help from there. 

 

I would recommend the macro approach, but if not, then PROC TRANSPOSE and then the DATA STEP.  Those are the options from most dynamic to least dynamic.

 

 

Super User
Posts: 24,012

Re: How do I accomplish a dynamic "transpose" with multiple variables?

[ Edited ]

After another review, that wouldn't work. You need to first add another variable so that the data will line up properly. 

 

You're looking to add an enumeration variable, which I initially thought could be intNonUniqID

 

The variable would be 1, 2, 1, 2 for your sample data. It allows the variables to line up, so Address1 is always under the same variable. 

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

 

data count_var;
set have;

by strUniqID;

if first.strUniqID then count=0;
count+1;

run;

 

Then you can use the approaches illustrated in my first post.

Super User
Posts: 6,934

Re: How do I accomplish a dynamic "transpose" with multiple variables?

[ Edited ]

While the examples in @Reeza's links may not be an exact match, they should be easily adaptable.  For example:

 

proc transpose data=have out=wide1 (drop=_name_) prefix=intNonUniqID_;

   var intNonUniqID;

   by strUniqID;

run;

proc transpose data=have out=wide2 (drop=_name_) prefix=strAddress_;

   var strAddress;

   by strUniqID;

run;

proc transpose data=have out=wide3 (drop=_name_) prefix=dteAppDate_;

   var dteAppDate;

   by strUniqID;

run;

proc transpose data=have out=wide4 (drop=_name_) prefix=intAppNum_;

   var intAppNum;

   by strUniqID;

run;

 

data want;

* retain ;

merge wide1 wide2 wide3 wide4;

by strUniqID;

run;

 

You may need to fill out the RETAIN statement, if you want to control the order of the variables.

Super User
Posts: 8,218

Re: How do I accomplish a dynamic "transpose" with multiple variables?

Actually, the macro that @Reeza mentioned does do what you want. If no ID is mentioned, it automatically creates one. I ran the following:

%transpose(data=have, out=want, by=strUniqID, delimiter=_,
 var=intNonUniqID strAddress dteAppDate intAppNum)

Art, CEO, AnalystFinder.com

Contributor
Posts: 20

Re: How do I accomplish a dynamic "transpose" with multiple variables?

[ Edited ]

I wish that were the case, but doing that breaks for me. As I mentioned earlier, I've seen that macro before and have been playing with it to get it to do what I want. Thus far though, no joy. Thanks for chiming in though.

Super User
Posts: 8,218

Re: How do I accomplish a dynamic "transpose" with multiple variables?

What do you mean by "breaks"? I ran the following:

data have;
  infile cards dlm='09'x;
  format dteAppDate mmddyy10.;
  input strUniqID $	intNonUniqID	strAddress $	dteAppDate mmddyy10.	intAppNum;
  cards;
A12345	1	123 45th St	1/2/1970	47
A12345	2	234 56th St	3/4/1978	39
B12345	3	345 67th St	8/6/1982	35
B12345	4	456 78th St	12/31/1994	22
;

then I ran the transpose macro (that I downloaded). Then I ran:

%transpose(data=have, out=want, by=strUniqID, delimiter=_,
 var=intNonUniqID strAddress dteAppDate intAppNum);
 

A snapshot of my results follows:

 

Capture.JPGOutput of resulting file

Art, CEO, AnalystFinder.com

Super User
Posts: 24,012

Re: How do I accomplish a dynamic "transpose" with multiple variables?

[ Edited ]

H4xc1ty wrote:

I wish that were the case, but doing that breaks for me. As I mentioned earlier, I've seen that macro before and have been playing with it to get it to do what I want. Thus far though, no joy. Thanks for chiming in though.


Given what you've presented, the approaches outlined should work. This isn't me (or anyone else here) trying to be difficult, it means that from reading your question, data and answering hundreds if not thousands of these types of questions, there's nothing that explicitly differentiates your question.

 

If this is a special case, you need to show us what you've done so far and why it doesn't work. 

Saying something doesn't work or 'no joy' isn't informative. 

 

EDIT: I tested it using @art297 sample data and the multiple PROC TRANSPOSE method works as designed after the addition of the count variable. 

 

 

Super User
Posts: 10,580

Re: How do I accomplish a dynamic "transpose" with multiple variables?

It comes back to the same old, same old:

POST YOUR DATA IN A DATA STEP!

That way everybody here can test their code with EXACTLY the data you have. Without that, it's often guesswork, and you and the other posters are not talking about the same thing.

So please put some effort into it and post your data in the correct form; if you have trouble, use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your dataset to a data step.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎11-20-2017 09:34 AM
Super User
Posts: 10,850

Re: How do I accomplish a dynamic "transpose" with multiple variables?

Check the MERGE skill proposed by me,Arthur.T,Matt.

 

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

 

 

 

data have;
  infile cards expandtabs truncover;
 
  input strUniqID $	intNonUniqID	strAddress & $20.	dteAppDate : mmddyy10.	intAppNum;
format dteAppDate  mmddyy10.;
cards;
A12345	1	123 45th St	 1/2/1970	47
A12345	2	234 56th St	 3/4/1978	39
B12345	3	345 67th St	 8/6/1982	35
B12345	4	456 78th St	 12/31/1994	22
;

data have;
 set have;
 by strUniqID;
 if first.strUniqID then n=0;
 n+1;
run;

proc sql noprint;
select distinct catt('have(where=(n=',n,') 
 rename=(intNonUniqID=intNonUniqID_',n,'
        strAddress=strAddress_',n,'
        dteAppDate=dteAppDate_',n,'
        intAppNum=intAppNum_',n,'))')  into : list separated by ' '
 from have;
 quit;

 %put list ;

 data want; 
  merge &list ;
  by strUniqID;
  drop n;
run;
Contributor
Posts: 20

Re: How do I accomplish a dynamic "transpose" with multiple variables?

YES!!! This is exactly what I need!!! Thanks so much! Also, I offer my apologies to everyone if I wasn't being clear or providing enough information. It was not my intention to be vague. I appreciate all of the responses greatly!
Super User
Posts: 8,218

Re: How do I accomplish a dynamic "transpose" with multiple variables?

Unless I'm missing something, the approach that @Ksharp recommended produces the same result as the approach I suggested, but requires a lot more code and takes 14 times longer to run.

 

Are you "sure" it is producing what you want?

 

Art, CEO, AnalystFinder.com

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 322 views
  • 6 likes
  • 6 in conversation