topic Re: transpose in SAS Programming

transpose

Teresa12 — Mon, 16 Aug 2021 14:04:28 GMT

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 !!!

Re: transpose

ballardw — Mon, 16 Aug 2021 14:45:10 GMT

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

Re: transpose

Teresa12 — Mon, 16 Aug 2021 15:03:25 GMT

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
;

Re: transpose

ballardw — Mon, 16 Aug 2021 15:14:53 GMT

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.

Re: transpose

Teresa12 — Mon, 16 Aug 2021 16:15:51 GMT

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!!