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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

9 REPLIES 9
Cynthia_sas
SAS Super FREQ
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
alep92
Fluorite | Level 6

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

Reeza
Super User

Are you guaranteeing only 2 quarters and codes are possible?

alep92
Fluorite | Level 6

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.

Reeza
Super User

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.

Reeza
Super User

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

Ksharp
Super User

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;
pkfamily
Obsidian | Level 7

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

LinusH
Tourmaline | Level 20
What is your desired output?
And why do you want do this transpose?
Data never sleeps

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
  • 9 replies
  • 1872 views
  • 7 likes
  • 6 in conversation