## help with transposing data

Solved
Occasional Contributor
Posts: 7

# help with transposing data

Hi!

Sorry if this was discussed but I couldn't find my particular problem:

I have a file that contains several thousand cases and only one variable ( I called it 'dummy') like this

Name Person 1

Name Person 2

Name Person 3

and I would like a 'normal' data set with the variables Name, Address1 and Address2.

I am very grateful for any help.

Andreas

Accepted Solutions
Solution
‎07-05-2011 01:27 PM
PROC Star
Posts: 8,164

## Re: help with transposing data

FWIW, that is exactly what my suggested code did, but with only one datastep and only requiring one pass through the data.

Art

All Replies
Contributor
Posts: 59

## help with transposing data

I think you can use Data step to deal with it.

data dummy;

cards;

Name_Person_1

Name_Person_2

Name_Person_3

;

run;

PROC Star
Posts: 8,164

## help with transposing data

You might be looking for something like:

data want (drop=dummy);

set have;

if mod(_n_,3) eq 1 then Name=dummy;

else if mod(_n_,3) eq 2 then Address=dummy;

else do;

output;

end;

run;

HTH,

Art

Super User
Posts: 10,770

## help with transposing data

Hi. Art.T

But If one person has more than two address?

and the number of address is vary?

Ksharp

PROC Star
Posts: 8,164

## help with transposing data

Ksharp,

I don't think it is a question of how complex one could make the problem but, rather, what the OP was really trying to solve.  Only he or she can say whether what any of us propose actually can serve as a potential solution.  Given what little information was provided, I would (and will) wait until they write back before losing any sleep over potential complexities.

Art

Super User
Posts: 10,770

OK.

Super User
Posts: 10,770

## help with transposing data

It is some complicated.

```data temp;
input dummy \$50.;
cards;
Name Person1
Name Person2
Name Person3
;
run;
data temp;
set temp;
if dummy eq: 'Name' then count+1;
run;
data _null_;
set temp end=last;
by count notsorted;
if _n_ eq 1 then call execute('data want;');
if first.count then n=0;
if dummy ne: 'Name' then  n+1;
if dummy eq: 'Name' then call execute('name="'||dummy||'";');
if last.count then call execute('output;');
if last then call execute('run;');
run;

```

Ksharp

Valued Guide
Posts: 533

## help with transposing data

...

The example is far from complete but; it just illustrates the use of the slash as a way to move the input to the next line.

Good luck,

- Jan.

Occasional Contributor
Posts: 7

## help with transposing data

Thanks a lot all of you. Ksharp's example works fine (I have to find out why and how). The only small thing is that since the string 'Name' is not in my dummy variable, I end up with three variables called address1-address3 but I can rename variables. I had cleaned up the data before, removing phone and fax numbers (there were different numbers of them but luckily we don't need them at the moment).

I have different data files in different exotic formats so this may help me in figuring them out myself, the principle is the same. Thanks again to all of you for your fast and helpful responses!

Occasional Contributor
Posts: 7

## Re: help with transposing data

Now I feel stupid, I don't know if I only tried with the test data earlier but it doesn't work, since the data doesn't have the text 'Name' in it, I guess.

I need to create a 'count' variable that is 1 for the first 3 observations, 2 for the next 3 and so on, then the code would work.

Hmm, I had an idea and the result looks ok, I will check.

Occasional Contributor
Posts: 7

## Re: help with transposing data

Not elegant (I needed two steps to create the ID) but it works:

data tmp2;

set tmp1;

count= MOD(_N_,3);

run;

data tmp3;

set tmp2;

if count=0 then count=3;

run;

data want (drop=dummy);

set tmp3;

if count=1 then Name=dummy;

else do;

output;

end;

run;

At first it didn't work but it just showed me some data I hadn't cleaned up.

Thanks all of you!

Solution
‎07-05-2011 01:27 PM
PROC Star
Posts: 8,164

## Re: help with transposing data

FWIW, that is exactly what my suggested code did, but with only one datastep and only requiring one pass through the data.

Art

Occasional Contributor
Posts: 7

## Re: help with transposing data

Yes, thank you, you can see I used your code. It didn't work at first, probably because 'address2' was not retained.

It is easy and I can understand how it works. I had actually created the count variable myself (but as I saw afterwards using the same function you used) and at the time I thought I needed it to be 1, 2, 3 but of course 1, 2, 0 works just as well.

PROC Star
Posts: 8,164

## Re: help with transposing data

You don't have to retain Address2 as the record is being output as soon as you assign Address2.  Variables only have to be retained if you need to keep them across iterations.

Art

Occasional Contributor
Posts: 7

## Re: help with transposing data

You're right again. My problems with running your code were caused entirely by the erroneous cases that were still in my data set but I didn't know it at the time.

🔒 This topic is solved and locked.