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,784

## 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=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
Posts: 3,852

## 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,784

## Re: Concise Data Set

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

Ksharp
Contributor
Posts: 45

## Re: Concise Data Set

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

## Re: Concise Data Set

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

Ksharp
Valued Guide
Posts: 653

## 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]
Discussion stats
• 6 replies
• 265 views
• 0 likes
• 4 in conversation