DATA Step, Macro, Functions and more

Proc transpose

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Proc transpose

Hi guys,

I'm a new SAS programmer so I'm sorry if I ask you an easy question and I use SAS studio.

I have a dataset made of 3 variables (qtr code id), I want obtain a dataset with id variable that contains every observations of the input dataset but repeated only once and a number of variables that correspond to the maximum frequency of the id in the input dataset.

I ask you if there is a way to get this output with only one proc transpose and not by using two proc transpose and then merge the output.

This is my code but it doesn't produce the desired output.


data ex.data;
input id qtr code $;
datalines;
1 2 A
2 3 C
2 1 B
3 2 A
4 3 C
5 1 D
5 1 A
6 3 A
;
run;
proc transpose data=ex.data out=ex.out;
by id;
var qtr code;
run;

 

Thanks,

Alessandro


Accepted Solutions
Solution
‎03-13-2016 05:25 AM
Super User
Posts: 9,671

Re: Proc transpose

Yes. This kind of question have been talked about many time. You could search it at this forum and get the answer.

The simples way is to use proc mean+ idgroup :

 

data data;
input id qtr code $;
datalines;
1 2 A
2 3 C
2 1 B
3 2 A
4 3 C
5 1 D
5 1 A
6 3 A
;
run;

proc sql;
select max(n) into : n
 from (select count(*) as n from data group by id);
quit;
proc summary data=data;
by id;
output out=want idgroup(out[&n] (qtr code)=);
run;

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,742

Re: Proc transpose

Can you explain what your desired output would look like, given the sample code you've posted?? Why did you decide on a PROC TRANSPOSE approach? It sounds to me like you want PROC FREQ.

cynthia
Occasional Contributor
Posts: 9

Re: Proc transpose

The output that I desire look like this:

 

id  qtr1  qtr2  code3  code4
1  2        .        A
2  3       1        C         B
3  2        .        A
4  3        .        C
5  1       1        D         A
6  3        .        A

Super User
Posts: 17,784

Re: Proc transpose

[ Edited ]

Are you guaranteeing only 2 quarters and codes are possible?

Occasional Contributor
Posts: 9

Re: Proc transpose

No there could be more than 2 quarters and codes, it depends on the input dataset.

This happens if in the input dataset there are more than 2 observations with the same id.

Super User
Posts: 17,784

Re: Proc transpose

Ok, can you please post some data that's more representative of your structure. Is there a max of 4 quarters for example? Can you end up with duplicate quarters?

 

You can create a data step of your data by using the techniques illustrated here:

http://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

 

You realize that the solution will still likely be at least more than one step?  

 

Your original question was:

I ask you if there is a way to get this output with only one proc transpose and not by using two proc transpose and then merge the output.

The answer is yes.  

 

However, you haven't explained why, which is important. We may end up suggesting solutions that have the same issue, because we don't understand the underlying requirements. That's a waste of everyone's time.

Super User
Posts: 17,784

Re: Proc transpose

Also, have you tried he data step method of transposing?

There's an example here:

 

http://www.ats.ucla.edu/stat/sas/modules/longtowide_data.htm

Solution
‎03-13-2016 05:25 AM
Super User
Posts: 9,671

Re: Proc transpose

Yes. This kind of question have been talked about many time. You could search it at this forum and get the answer.

The simples way is to use proc mean+ idgroup :

 

data data;
input id qtr code $;
datalines;
1 2 A
2 3 C
2 1 B
3 2 A
4 3 C
5 1 D
5 1 A
6 3 A
;
run;

proc sql;
select max(n) into : n
 from (select count(*) as n from data group by id);
quit;
proc summary data=data;
by id;
output out=want idgroup(out[&n] (qtr code)=);
run;
Contributor
Posts: 24

Re: Proc transpose

What an elegant answer! However, the proc sql portion is a bit confusing for me. I will have to put aside some time to truly understand the code. 

 

Thank you for your answer.

 

-Victor

Super User
Posts: 5,256

Re: Proc transpose

What is your desired output?
And why do you want do this transpose?
Data never sleeps
☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 498 views
  • 7 likes
  • 6 in conversation