Concise Data Set

[JAR]

After doing a PROC TRANSPOSE, I got this data.



Quarter Prod1 Prod2 Prod3
1, 685.35, ., .,
2, 2925.55, ., .,
3, 469553.6, ., .,
4, 1099.85, ., .,
1, ., 10220.51, .,
1, ., ., 1510.16,
2, ., ., 612346.89,
3, ., ., 158393.23,


I wish to put them togethet in such manner that quarter does not repeate.

Quarter Prod1 Prod2 Prod3
1, 685.35, 10220.51, 1510.16,
2, 2925.55, ., 612346.89,
3, 469553.6, ., 158393.23,
4, 1099.85, ., .

What is an efficient way of approaching this problem?

Regards,
Jijil Ramakrishnan

[Ksharp]

OK.
Easy.
[pre]



data temp;
infile datalines dlm=' ,';
input Quarter Prod1 Prod2 Prod3 ;
datalines;
1, 685.35, ., .,
2, 2925.55, ., .,
3, 469553.6, ., .,
4, 1099.85, ., .,
1, ., 10220.51, .,
1, ., ., 1510.16,
2, ., ., 612346.89,
3, ., ., 158393.23,
;
run;
proc sort data=temp;
by quarter;
run;
data want(drop=prod:);
set temp;
retain _prod1 _prod2 _prod3;
by quarter;
if first.quarter then call missing(of _prod1 _prod2 _prod3);
_prod1=coalesce(_prod1,prod1);
_prod2=coalesce(_prod2,prod2);
_prod3=coalesce(_prod3,prod3);
if last.quarter then output;
run;
[/pre]

Ksharp

[data_null__]

The UPDATE statement is appropriate to this problem.

[pre]
data have;
infile cards dsd;
input Quarter Prod1-Prod3;
cards;
1,685.35,.,.,
2,2925.55,.,.,
3,469553.6,.,.,
4,1099.85,.,.,
1,.,10220.51,.,
1,.,.,1510.16,
2,.,.,612346.89,
3,.,.,158393.23,
;;;;
run;
proc sort data=have;
by quarter;
run;
data need;
update have(obs=0) have;
by quarter;
run;
proc print;
run;
[/pre]

[Ksharp]

Your code is great.I must missed something in docutmentation.


Ksharp

[JAR]

Awesome!!
Thanks a million!

[Ksharp]

Hi.
Do you try the _null_'s code?
I think he is more clever tham me. 🙂


Ksharp

[ArtC]

You might be able to solve this on the TRANSPOSE side. My guess would be that you did not use the ID statement. the transpose would look something like:

[pre]
proc transpose data=old out=new;
by quarter;
id prod;
run;
[/pre]