topic Re: Expansion from rows to columns? in SAS Programming

Expansion from rows to columns?

almmotamedi — Wed, 24 Feb 2016 19:17:14 GMT

Hi SAS users, I have a dataset as below:

Variable1 Variable2 Variable3

1 W 3.2

1 X 5.1

2 X 7.0

3 E 8.9

3 W 1.3

Whenever, the Variable2 corresponding to Variable1 (ID) is changing its value, I would like to expand it to new columns. In fact per each new category (based on Variable2), two new columns are added to the right:

Variable1 Variable2 Variable3 Variable4 Variable5

1 W 3.2 X 5.1

2 X 7.0 null null

3 E 8.9 W 1.3

PURPOSE: At the end, I am interested to have a unique Variable1 in first column; and the corresponding category(ies) are expanded to the right instead of stacking on top of eachother.

Could you please help how to do it?

Re: Expansion from rows to columns?

Reeza — Wed, 24 Feb 2016 19:22:34 GMT

You've asked the question here:

https://communities.sas.com/t5/Base-SAS-Programming/How-to-expand-columns-in-a-dataset/m-p/252081#M47754

And had several suggestions and links to solutions.

Is there something specific that you're having issues with? Or do you want an exact solution to exactly your problem with full code?

Re: Expansion from rows to columns?

almmotamedi — Wed, 24 Feb 2016 20:16:51 GMT

None of those "And had several suggestions and links to solutions." were useful.

By the way, I don't want a full code and exact solution to my problem. I am re-posting my question so that new people/users can easily follow and read my question and give me some hints.

Re: Expansion from rows to columns?

FreelanceReinh — Wed, 24 Feb 2016 21:19:41 GMT

@almmotamedi wrote:

None of those "And had several suggestions and links to solutions." were useful.

Hi @almmotamedi,

I can hardly believe that. For example, which of the four techniques from that link I provided in the other thread did you try? What kind of issues did you encounter that you didn't find them useful? I was pretty sure that at least two or three of those solutions could be successfully applied to your data.

Re: Expansion from rows to columns?

Reeza — Wed, 24 Feb 2016 23:54:57 GMT

Ok questions - please answer as clearly and thoroughly as possible.

1. Is it actually only two variables or are there more?

2. What will be the naming conventions for new variables? Are the old variable names changing?

3. What is the maximum number of vqriable additions? Are you only adding on 2 new variables? Do you know it in advance or do you have do dynamically figure it out?

4. Is this a one time job, or do you need to repeat it.

5. Post anything you've tried and highlight what issues you're having....there's a point when people start to feel taken advantage of. Posting what you've tried shows that you're asking for help, not for other people to do your work.

Re: Expansion from rows to columns?

LinusH — Thu, 25 Feb 2016 12:31:07 GMT

Also, motivate why you want to do this? What is the inconvenience of having the data normalized? How do you intend to use the transposed data set?

Re: Expansion from rows to columns?

lxn1021 — Thu, 25 Feb 2016 17:27:17 GMT

Try this (tested):

data letters;
input Variable1 Variable2 $ Variable3 $5.;
datalines;
1 W 3.2
1 X 5.1
2 X 7.0
3 E 8.9
3 W 1.3
;
run;

data nodup;
set letters;
by variable1;
if first.variable1=1 then output;
run;

data dup;
set letters;
by variable1;
if first.variable1=0 then output;
rename variable2=Variable4 variable3=Variable5;
run;

data nodup_dup;
merge nodup dup;
by variable1;
if variable4="" then variable4="NULL";
if variable5="" then variable5="NULL";
run;