topic Re: How do I accomplish a dynamic "transpose" with multiple variables? in SAS Programming

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

H4xc1ty — Sun, 19 Nov 2017 20:09:17 GMT

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!!

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

Reeza — Sun, 19 Nov 2017 20:22:14 GMT

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

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

H4xc1ty — Sun, 19 Nov 2017 20:30:57 GMT

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.

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

Reeza — Sun, 19 Nov 2017 20:47:07 GMT

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.

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

Reeza — Sun, 19 Nov 2017 20:59:15 GMT

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.

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

Astounding — Sun, 19 Nov 2017 21:42:45 GMT

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.

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

art297 — Sun, 19 Nov 2017 22:15:55 GMT

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

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

H4xc1ty — Sun, 19 Nov 2017 22:35:42 GMT

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.

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

art297 — Sun, 19 Nov 2017 22:44:38 GMT

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:

Output of resulting file

Art, CEO, AnalystFinder.com

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

Reeza — Mon, 20 Nov 2017 00:36:19 GMT

@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.

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

Kurt_Bremser — Mon, 20 Nov 2017 08:17:40 GMT

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-data-AKA-generate/ta-p/258712 to convert your dataset to a data step.

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

Ksharp — Mon, 20 Nov 2017 13:17:23 GMT

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;

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

H4xc1ty — Mon, 20 Nov 2017 14:33:56 GMT

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!

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

art297 — Mon, 20 Nov 2017 17:25:55 GMT

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