topic Re: Concise Data Set in SAS Programming

Concise Data Set

JAR — Wed, 08 Jun 2011 09:16:00 GMT

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

Re: Concise Data Set

Ksharp — Wed, 08 Jun 2011 09:47:23 GMT

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

Re: Concise Data Set

data_null__ — Wed, 08 Jun 2011 11:12:39 GMT

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]

Re: Concise Data Set

ArtC — Wed, 08 Jun 2011 18:20:31 GMT

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]

Re: Concise Data Set

Ksharp — Thu, 09 Jun 2011 02:07:05 GMT

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

Ksharp

Re: Concise Data Set

JAR — Thu, 09 Jun 2011 05:05:40 GMT

Awesome!!
Thanks a million!

Re: Concise Data Set

Ksharp — Thu, 09 Jun 2011 06:19:13 GMT

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

Ksharp