Desktop productivity for business analysts and programmers

Fillin in data after Transpose

Reply
Occasional Contributor
Posts: 13

Fillin in data after Transpose

HI All,

I am trying to do some Regression analysis and the table that I am using as my input has some data gaps.

Thesis what I mean by it. I used "Spit Column" function in the EG in order to create a Cross tab Table from a flat table. However as is the case in some cases there is some missing data here and there.

So my question is how do I make empty points (.) become 0 so that I can do my regression analysis.

For general reference I have showed how data looks after transposing the input table.

CompanyProduct 1

Product 2

Product 3Product 4
1$5.$200$70
2..$23.
3$40$70.$100
4$23.$44$1

P.S. There are about 70 Products so i tough about writing a new query and doing computations on each of the new columns but that just seems crazy.

Thanks for any help you can offer.

Super User
Posts: 11,144

Re: Fillin in data after Transpose

What kind of analysis were you planning?

It may be that your data structure would work better as: Company ProductDescription Value. Then you wouldn't have any "gaps".

Occasional Contributor
Posts: 13

Re: Fillin in data after Transpose

well I need to see what Products have the highest impact on Product1.

PROC Star
Posts: 1,146

Re: Fillin in data after Transpose

Give this a try:

proc stdize data=have out=want reponly MISSING=0;

var Product1-Product4;

run;

Tom

Occasional Contributor
Posts: 13

Re: Fillin in data after Transpose

Hi TomKari, the image bellow is the process I have in my EG Project. Where would you recommend i place your code?

PROC Star
Posts: 1,146

Re: Fillin in data after Transpose

Put it in as a code node (File | New | Program), link the output dataset from your "Split Columns" to it, and replace the dataset name "have" with the name of the dataset created by your split columns.

Tom

Ask a Question
Discussion stats
  • 5 replies
  • 360 views
  • 6 likes
  • 3 in conversation