Expansion from rows to columns?

[almmotamedi]

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?

[Reeza]

You've asked the question here:

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

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?

[almmotamedi]

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.

[FreelanceReinh]

---

@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.

[Reeza]

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.  

[LinusH]

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?

[lxn1021]

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;