Year by horizontal data

[vijay1]

Have
year name sales
2001 john 1000
         jack 2000
2002 john 3000
         jack 4000

Want
year name sales year name sales
2001 john 1000 2002 john 3000
 2001 jack 2000 2002 jack 4000

How can I get the above output in excel ? please check have and want tables.

[andreas_lds]

Looks like proc transpose could be used, but only if there are no missing values in "year".

To get further help, you may want to post your data in usable form. The data-layout you want is in general not recommended, except for reports.

[vijay1]

The below is fine too

Have
year name sales
2001 john 1000
 2001 jack 2000
2002  john 3000
 2002  jack 4000

Want
year name sales year name sales
2001 john 1000 2002 john 1000
 2001 jack 2000  2002 jack 2000

[PaigeMiller]

---

@vijay1 wrote:

The below is fine too

Have
year name sales
2001 john 1000
 2001 jack 2000
2002  john 3000
 2002  jack 4000

Want
year name sales year name sales
2001 john 1000 2002 john 1000
 2001 jack 2000  2002 jack 2000

---

I hope you realize that you can't have data sets with multiple columns each with identical variable names. You can have SALES as the variable name only once. You can have YEAR as the variable name only once.

Also, why do you want this? What is the next step? Are you going to produce a report, or are you going to do some data analysis? What? Normally this type of re-arranging of data is not going to be useful (although there are exceptions) and makes subsequent programming more difficult.

[vijay1]

I made the correction:

Have
year name sales
2001 john 1000
2001 jack 2000
2002 john 3000
2002 jack 4000

Want
year name sales year name sales
2001 john 1000 2002 john 3000
2001 jack 2000 2002 jack 4000

[Reeza]

There's been two solutions posted already - do none work for you?

[Reeza]

Are you sure that's what you want?

Wouldn't something like this make more sense:

Name Sales2001 Sales2002
John 1000 3000
Jack 2000 4000

And most importantly, HOW are the numbers changing? How do you go from 3000 to 1000?

---

@vijay1 wrote:

The below is fine too

Have
year name sales
2001 john 1000
 2001 jack 2000
2002  john 3000
 2002  jack 4000

Want
year name sales year name sales
2001 john 1000 2002 john 1000 <- What is the rule to change the values?
 2001 jack 2000  2002 jack 2000

---

[andreas_lds]

---

@vijay1 wrote:

The below is fine too

---

No, it is not. As others already said: you can't have multiple variables with the same name in a dataset. So until you have fixed the obvious issue in your wanted result, it is hardly possible to help you. It is also still not clear whether you want the result as report or dataset, the later would raise the question what you plan to do with such a dataset.

If you want a report, check the following code, maybe this layout is acceptable, too.

proc sort data=have out=sorted;
   by name year;
run;

proc report data=sorted;
   columns name year,sales;
   
   define name / group;
   define year / across;
   define sales / sum; /* only one value per name/year so no effect */
run;

[ballardw]

Do you want a report, people read these, or a data set for further manipulation? If you want a data set SAS will not allow you to have multiple variables with the same name. You would have to have something in the output like Name2001 and Name2002

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values as your SAS data set.

---

@vijay1 wrote:

Have
year name sales
2001 john 1000
         jack 2000
2002 john 3000
         jack 4000

Want
year name sales year name sales
2001 john 1000 2002 john 1000
         jack 2000           jack 2000

How can I get the above format? please check have and want tables. attached the same excel file too

---

[Ksharp · Accepted solution]

data have;
input year name $ sales;
id=name;
cards;
2001 john 1000
 2001 jack 2000
2002  john 3000
 2002  jack 4000
;

proc sort data=have;by name;run;
proc sql noprint;
select distinct catt('have(rename=(year=year',year,' name=name',year,' sales=sales',year,')
 where=(year',year,'=',year,'))') into : merge separated by ' '
 from have ;
quit;

data want;
 merge &merge. ;
 by id;
 drop id;
run;