BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Teresa12
Fluorite | Level 6


Hi
I need to transpose a dataset and I don't know how to do it

I have

data have;
length names $50.;
input names $ ;
datalines;
ana\ marc\ rose
mary \sa \ann \marie paul
ines
;


and I want a dataset :

Names
ana
marc
rose
mary
sa
ann
marie paul
ines

Thank you !!!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Note that I renamed the variable to NAME in my data step to read the individual names because now it only had one name and used the @@ to read multiple values from a single line of text.

 

If you have a question about code that you run the best practice to copy the code and any messages from the log, open a text box on the forum using the </> icon above the message window and then paste the code and messages. That way we can see exactly what code you ran and answer questions.

View solution in original post

4 REPLIES 4
ballardw
Super User

"Transpose" would mean to rearrange variable positions. You want to split values out of a variable and not move them.

 

First, make sure that your data step will create values properly.

You don't mention any other variables so this could be done by reading the data properly to begin with. Example:

data have;
  infile datalines dlm='\';
  informat name $12. ;
  input name @@ ;
  name=strip(name);
datalines;
ana\ marc\ rose
mary \sa \ann \marie paul
ines
;

The @@ on the input reads from the line until exhausted.

 

Assuming that you have a more complicated issue and your existing data set has multiple values in a single variable:

data have;
   infile datalines dlm=',';
length names $50.;
input names $ ;
datalines;
ana\ marc\ rose
mary \sa \ann \marie paul
ines
;

data want;
   set have;
   length name $ 12;
   do i=1 to countw(names,'\');
      name= strip(scan(names,i,'\'));
      output;
   end;
drop i; run;

The delimiter in the Have set allows us to read the data as a single variable.

The Want data set counts how many individual names are separated by the \ character, then uses Scan to pull each one out. Strip is to remove leading spaces that would occur.

The output in the loop writes to the output data set once for each name scanned.

Teresa12
Fluorite | Level 6

Ballardaw, thank you very much for your answer, It works!!!

Thank you for your explanation!!

The only problem now, when the line starts by \, the column Name is missing

 

data have;
   infile datalines dlm=',';
length names $50.;
input names $ ;
datalines;
ana\ marc\ rose
mary \sa \ann \marie paul
ines
\bert \tt ;

 

ballardw
Super User

Note that I renamed the variable to NAME in my data step to read the individual names because now it only had one name and used the @@ to read multiple values from a single line of text.

 

If you have a question about code that you run the best practice to copy the code and any messages from the log, open a text box on the forum using the </> icon above the message window and then paste the code and messages. That way we can see exactly what code you ran and answer questions.

Teresa12
Fluorite | Level 6

Hi Ballardw

Thank you again for your answer

With 

data have;
  infile datalines dlm='\';
  informat name $12. ;
  input name @@ ;
  name=strip(name);
datalines;
ana\ marc\ rose
mary \sa \ann \marie paul
ines
;

 

my program works perfectly

 

Thank you again!! 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1291 views
  • 0 likes
  • 2 in conversation