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
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;
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
Are you guaranteeing only 2 quarters and codes are possible?
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.
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.
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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.