DATA Step, Macro, Functions and more

Concise Data Set

Reply
Contributor JAR
Contributor
Posts: 45

Concise Data Set

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
Super User
Posts: 10,020

Re: Concise Data Set

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=prodSmiley Happy;
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
Respected Advisor
Posts: 3,799

Re: Concise Data Set

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]
Super User
Posts: 10,020

Re: Concise Data Set

Posted in reply to data_null__
Your code is great.I must missed something in docutmentation.


Ksharp
Contributor JAR
Contributor
Posts: 45

Re: Concise Data Set

Awesome!!
Thanks a million!
Super User
Posts: 10,020

Re: Concise Data Set

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


Ksharp
Valued Guide
Posts: 634

Re: Concise Data Set

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]
Ask a Question
Discussion stats
  • 6 replies
  • 233 views
  • 0 likes
  • 4 in conversation