topic Re: Proc transpose in SAS Programming

Proc transpose

alep92 — Sat, 12 Mar 2016 14:58:59 GMT

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

Re: Proc transpose

Cynthia_sas — Sat, 12 Mar 2016 15:11:28 GMT

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

Re: Proc transpose

LinusH — Sat, 12 Mar 2016 15:14:26 GMT

What is your desired output?
And why do you want do this transpose?

Re: Proc transpose

alep92 — Sat, 12 Mar 2016 16:50:21 GMT

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

Re: Proc transpose

Reeza — Sat, 12 Mar 2016 17:59:39 GMT

Are you guaranteeing only 2 quarters and codes are possible?

Re: Proc transpose

Reeza — Sat, 12 Mar 2016 17:11:53 GMT

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

Re: Proc transpose

alep92 — Sat, 12 Mar 2016 17:19:32 GMT

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.

Re: Proc transpose

Reeza — Sat, 12 Mar 2016 18:06:09 GMT

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.

Re: Proc transpose

Ksharp — Sun, 13 Mar 2016 02:50:16 GMT

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;

Re: Proc transpose

pkfamily — Fri, 02 Dec 2016 20:55:14 GMT

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