BookmarkSubscribeRSS Feed
almmotamedi
Obsidian | Level 7

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?

6 REPLIES 6
Reeza
Super User

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
Obsidian | Level 7

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
Jade | Level 19

@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
Super User

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
Tourmaline | Level 20

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?

Data never sleeps
lxn1021
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1190 views
  • 0 likes
  • 5 in conversation