BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
H4xc1ty
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

13 REPLIES 13
Reeza
Super User

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

 

 

H4xc1ty
Fluorite | Level 6

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.

Reeza
Super User

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.

 

 

Reeza
Super User

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.

Astounding
PROC Star

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.

art297
Opal | Level 21

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

H4xc1ty
Fluorite | Level 6

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.

art297
Opal | Level 21

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 fileOutput of resulting file

Art, CEO, AnalystFinder.com

Reeza
Super User

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

 

 

Kurt_Bremser
Super User

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.

Ksharp
Super User

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;
H4xc1ty
Fluorite | Level 6
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!
art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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