Getting different results with proc transpose

[jerry898969]

I'm transposing a table that has ID, Year and Name. The data looks like this


1 1990 Bob
1 1991 Ken
1 1997 Cindy
1 1998 Will
2 1990 Jack
2 1991 Frank
2 1992 Mindy

I was expecting my data to look like this
ID 1990 1991 1992 1997 1998
1 Bob Ken Cindy Will
2 Jack Frank Mindy

But what is happening is that it comes out like this
ID 1990 1991 1997 1998 1992
1 Bob Ken Cindy Will
2 Jack Frank Mindy

I'm guessing since my first id doesn't have all the years that is why this happens. What is the best way to get around this issue and have the years display in the correct order?

Thank you

[SPR]

Hello Jerry,

The easiest is to use RETAIN berfore SET on the result dataset:
[pre]data i;
input ID Year Name $;
datalines;
1 1990 Bob
1 1991 Ken
1 1997 Cindy
1 1998 Will
2 1990 Jack
2 1991 Frank
2 1992 Mindy
run;
proc transpose data=i out=t prefix=y;
id year;
var Name;
by id;
run;
data t;
retain ID y1990-y1998;
set t;
run;
[/pre]
Sincerely,
SPR

[jerry898969]

SPR,

Thank you for your reply. Isn't there a way to do it within the proc transpose?
I'm trying to do it in the least amount of data sets. The lead here gets mad if there is extra steps.

Thank you again it is appreciated

Jerry

[SPR]

Jerry,

It is impossible to do it with proc TRANSPOSE. Other possible solution could be to add and odservation having Year=1992 and Nane=" " to the input dataset. However, this is still additional step. (:-)

SPR

[data_null__]

> SPR,
> I'm trying to do it in the least amount of data sets.
> The lead here gets mad if there is extra steps.

Your "lead" may need to learn more SAS. This is an extra unneeded step.
[pre]
data class;
set sashelp.class;
run;
proc sort;
by sex;
run;
[/pre]
Where you would just as easily and more efficiently.
[/pre]proc sort data=sashelp.class out=class;[/pre]


As mentioned PROC TRANSPOSE will create the variables as they are encountered. Also mentioned to make the program data driven I would do something like this. There are no extra steps to achieve the desired result.

[pre]
data test;
input id:$1. year name:$8.;
cards;
1 1990 Bob
1 1991 Ken
1 1997 Cindy
1 1998 Will
2 1990 Jack
2 1991 Frank
2 1992 Mindy
;;;;
run;
proc summary data=test nway;
class year;
output out=year(drop=_:);
run;
data testV / view=testV;
set year test;
run;
proc transpose prefix=y data=testV out=wide(where=(not missing(id)));
by id;
var name;
id year;
run;
proc print;
run;
[/pre]


[pre]
Obs id _NAME_ y1990 y1991 y1992 y1997 y1998

1 1 name Bob Ken Cindy Will
2 2 name Jack Frank Mindy
[/pre]

[jerry898969]

SPR & data _null_,

Thank you both for all the help. I was able to use what you both provided to take care of this issue. If he has any problems with the way I do it I will have him show me a better way. LOL

Thank you again

Jerry

[DavidJ]

I'm glad my "lead" doesn't snoop around the code I write!

[Cynthia_sas]

Hi:
An alternate approach (just for something completely different). Using ACROSS items with PROC REPORT can result in something akin to what you get with TRANSPOSE. The difference is that other approaches do not require you to know the year values ahead of time, but if you are going to use RENAME with PROC REPORT, you would either need to know the values ahead of time or "macroize" the solution.

Using COMPLETECOLS with PRELOADFMT allows you to create "empty" columns for the years 1993-1996 that are not in the data (see #2) if that would be desired.

cynthia
[pre]
data names;
infile datalines;
input ID year name $;
return;
datalines;
1 1990 Bob
1 1991 Ken
1 1997 Cindy
1 1998 Will
2 1990 Jack
2 1991 Frank
2 1992 Mindy
;
run;

options nocenter ls=125;
ods listing;
proc print data=names;
title 'Before PROC REPORT';
run;

** 1) Use PROC REPORT to get output dataset with only the years in the data;
proc report data=names nowd
out=work.yearout(rename=(_c2_=yr1990 _c3_=yr1991 _c4_=yr1992 _c5_=yr1997 _c6_=yr1998 n=numyr )
drop=_break_);
title '1) PROC REPORT #1 creates work.yearout';
column ID year,name n;
define id /group;
define year /across;
define name/display ' ';
define n / 'Count' noprint;
run;

proc print data=yearout;
title '1) YEAROUT data from PROC REPORT';
run;

** 2) Get all years between 1990 and 1998 using PROC REPORT;
proc format;
value wantyr
1990='1990'
1991='1991'
1992='1992'
1993='1993'
1994='1994'
1995='1995'
1996='1996'
1997='1997'
1998='1998';
run;

proc report data=names nowd completecols
out=work.allyears(
rename=(_c2_=yr1990 _c3_=yr1991 _c4_=yr1992 _c5_=yr1993 _c6_=yr1994 _c7_=yr1995 _c8_=yr1996 _c9_=yr1997 _c10_=yr1998 n=numyr )
drop=_break_ );
title '2) PROC REPORT #2 creates work.allyears';
column ID year,name n;
define id /group;
define year /across preloadfmt f=wantyr.;
define name/display ' ';
define n / 'Count' noprint;
run;

proc print data=allyears;
title '2) ALLYEARS data from PROC REPORT';
run;
[/pre]

[deleted_user]

Hello,

if you like data steps you can use a proc sql-data step solution:

[pre]
*consecutive years;

proc sql noprint;
select cats('yr',min(year)), cats('yr',max(year)),min(year),max(year) into :yr_min, :yr_max, :lb, :hb separated by ' ' from names;
quit;


data want;

do until(last.id);
set names;
by id;

array yr{&lb:&hb} $ 12 &yr_min-&yr_max;
yr{year}=name;

end;

drop year name;
run;

*Universal;

proc sql noprint;
select cats('yr', years) into :all_y separated by ' ' from (select distinct(year) as years from names)
order by years ;
quit;

data want1;

do until(last.id);
set names;
by id;

z=countw(substr("&all_y",1,findw("&all_y",cats('yr',year))+5));

array yr{*} $ 12 &all_y;
yr{z}=name;

end;
drop year name z;
run;
[/pre]

Marius

[Ksharp]

Hi.
I looked so many replys ,So I also think it for a while.

data i;

  input
ID Year Name $;

datalines;

1 1990 Bob

1 1991 Ken

1 1997 Cindy

1 1998 Will

2 1990 Jack

2 1991 Frank

2 1992 Mindy

run;

proc sort data=i;

 by id year;

run;

proc sql;

 create table
temp as

  select
id,year

   from (select distinct id from

i),(select distinct
year from i)

    order by id,year;

quit;

data _temp;

 merge i temp;

 by id year;

run;

proc transpose data=_temp out=result(drop=_name_) prefix=_;

 by id;

 id year;

 var name;

run;

[pre]


Ksharp
[/pre] Message was edited by: Ksharp