topic Re: Transpose Data with groups in New SAS User

Transpose Data with groups

Singham20 — Tue, 30 Apr 2019 18:00:36 GMT

Hi all!

I am having trouble transposing my data.

Here is what I have:

Name	Month	Value	City
Jon	Jan	11	Orlando
Jon	Feb	42	Orlando
Jon	Mar	34	Orlando
Jon	Apr	82	Orlando
Jill	Jan	24	Miami
Jill	Feb	4	Miami
Jill	Mar	45	Miami
Cam	Jan	98	Houston
Cam	Feb	42	Houston
Cam	Mar	48	Houston
Cam	Apr	11	Houston
Terry	Feb	68	Austin
Terry	Apr	72	Houston

Here is what I need:

Name	Jan	Feb	Mar	Apr	City
Jon	11	42	34	82	Orlando
Jill	24	4	45		Miami
Cam	98	42	48	11	Houston
Terry		68		72	Error

I know proc transpose can be used to transpose however I do not know how to achieve my end table. Notice I put "Error" as the City for Terry; this is because Terry has more than one city for him and I want that to be flagged.

Does anyone have any thoughts on this?

Thank you so much for all your help!

Re: Transpose Data with groups

novinosrin — Tue, 30 Apr 2019 21:27:03 GMT


data have;
input Name $	Month $	Value	City $;
cards;
Jon	Jan	11	Orlando
Jon	Feb	42	Orlando
Jon	Mar	34	Orlando
Jon	Apr	82	Orlando
Jill	Jan	24	Miami
Jill	Feb	4	Miami
Jill	Mar	45	Miami
Cam	Jan	98	Houston
Cam	Feb	42	Houston
Cam	Mar	48	Houston
Cam	Apr	11	Houston
Terry	Feb	68	Austin
Terry	Apr	72	Houston
;

proc sql;
create table temp as
select Name,Month, Value, ifc(count(distinct city)>1,'Error',City) as  City
from have
group by name ;
quit;

proc transpose data=temp out=want;
by name city notsorted;
var value;
id month;
run;

Hi @Singham20 Since you mentioned "I know proc transpose can be used to transpose however I do not know how to achieve my end table."

I believe you can get the order of the month right by modifying my code in the previous step before tranpose to get the right order. If you are unable to, let us know. In that case, i will have to get over my laziness

Re: Transpose Data with groups

Singham20 — Tue, 30 Apr 2019 18:42:17 GMT

Thank you so much! This does work! I know how to get the order for the months if it was in numeric form however how would I order the months in there current form?

Thank you!

Re: Transpose Data with groups

novinosrin — Tue, 30 Apr 2019 18:49:02 GMT

@Singham20 Ok no worries, try this

data have;
input Name $	Month $	Value	City $;
cards;
Jon	Jan	11	Orlando
Jon	Feb	42	Orlando
Jon	Mar	34	Orlando
Jon	Apr	82	Orlando
Jill	Jan	24	Miami
Jill	Feb	4	Miami
Jill	Mar	45	Miami
Cam	Jan	98	Houston
Cam	Feb	42	Houston
Cam	Mar	48	Houston
Cam	Apr	11	Houston
Terry	Feb	68	Austin
Terry	Apr	72	Houston
;
data temp;
do until(last.name);
set have;
by name city notsorted;
if first.city then n=sum(n,1);
end;
do until(last.name);
set have;
by name city notsorted;
City=ifc(n>1,'Error',City);
output;
end;
drop n;
run;


proc transpose data=temp out=want;
by name city notsorted;
var value;
id month;
run;

Re: Transpose Data with groups

ballardw — Tue, 30 Apr 2019 22:30:26 GMT

@Singham20 wrote:

Thank you so much! This does work! I know how to get the order for the months if it was in numeric form however how would I order the months in there current form?

Thank you!

Anytime sort order of anything resembling dates or times comes up it is usually worth the effort to get a SAS date value and use an appropriate format.

Partially because just changing the format can give you different groups such as calendar quarter of the year.